CODEWITHSUNDEEP
c#asp.net-mvclinq
Peter
Peter

Reputation: 803

Linq to sql to return related data

I have two models which are related.

public class RolesModels
{
    public RolesModels()
    {
        this.Users = new HashSet<UserModels>();
    }

    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int RoleId { get; set; }

    [Required]
    [DataType(DataType.Text)]
    [StringLength(20, ErrorMessage = "The {0} must be at least 6 characters long.", MinimumLength = 6)]
    [Display(Name = "Caption")]
    public string Caption { get; set; }

    [Display(Name = "Can Create")]
    public bool createRole { get; set; }

    [Display(Name = "Can View")]
    public bool viewRole { get; set; }


    [Display(Name = "Can Modify")]
    public bool modifyRole { get; set; }

    [Display(Name = "Can Delete")]
    public bool deleteRole { get; set; }

    public virtual ICollection<UserModels> Users { get; set; }

}

and the second is like this

   public class UserModels
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]

    public int user_id { get; set; }

    [Required]
    [DataType(DataType.Text)]
    [Display(Name = "User Name")]
    [StringLength(20, ErrorMessage = "The {0} must be at least 3 characters long.", MinimumLength = 3)]
    public string user_name { get; set; }

    [Required]
    [DataType(DataType.Password)]
    [Display(Name = "Password")]
    [StringLength(10, ErrorMessage = "The {0} must be at least 4 characters long.", MinimumLength = 4)]
    public string user_pass { get; set; }

    [DataType(DataType.EmailAddress)]
    [Display(Name = "Email")]
    [StringLength(50, ErrorMessage = "The {0} must be at least 6 characters long.", MinimumLength = 6)]
    public string UserEmail { get; set; }

    [Display(Name = "Registeration Date")]
    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:dd-MM-yyyy}", ApplyFormatInEditMode = true)]
    public DateTime RegDate { get; set; }

    [Display(Name = "Enable User")]
    public bool status { get; set; }

    //[Bindable(false)]
    public int RoleId { get; set; }

    [Display(Name = "User Roles")]
    public virtual RolesModels Roles { get; set; }

}

The thing is, I want to be able to retrieve records with the following columns:

   User Name | Email | Registration Date | Enable User | Role Caption

here is my linq to sql code:

  var model = from c in db.UserModels
               join o in db.RolesModels on c.RoleId equals o.RoleId
               where 1==1
               select c;

Upvotes: 0

Views: 129

Answers (4)

Peter
Peter

Reputation: 803

I was able to figure it out using the Consoto University sample project. Here is the solution:

var m = db.UserModels.Include(u => u.Roles);
return View(m.ToList());

and modelItem => item.Roles.Caption to fetch the role caption from the Roles models using the navigational property.

Thanks all for the attempts.

Upvotes: 0

Deepesh
Deepesh

Reputation: 610

var model = (from c in db.UserModels.AsEnumerable()
               join o in db.RolesModels.AsEnumerable() on c.RoleId equals o.RoleId
               select new { c.user_name, c.UserEmail, c.RegDate, o.Caption,  c.status}).ToList();

Do something like this.

Upvotes: 0

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236208

You don't need where 1=1 part, because it gives you always true and does not affect result set. Also you already have navigation property for roles, so you don't need to join entities manually:

from u in db.UserModels
select new { 
  UserName = user_name,
  Email = UserEmail,
  RegistrationDate = RegDate,
  EnableUser = status,
  RoleCaption = u.Roles.Caption
}

Consider also to do null-check if it is possible that user can not have role assigned.

Upvotes: 1

COLD TOLD
COLD TOLD

Reputation: 13579

You mean like This 

var model = from c in db.UserModels
               join o in db.RolesModels on c.RoleId equals o.RoleId
               where 1==1
               select new { c.user_name, c.UserEmail, c.RegDate, o.Caption,  c.status}).ToList();

Upvotes: 0

Related Questions