Linq to SQL - Get Related Data

c# entity-framework entity-framework-6 linq-to-sql


I've got some data that I need to return some of its related data and put it all in a model. I have all the appropriate fields setup in my model, and looks like this:

public class ComputerModel
    public int MachineId { get; set; }
    public int GroupId { get; set; }
    public int SoftwareVersionId { get; set; }
    public string GroupName { get; set; }
    public string SoftwareVersion { get; set; }
    public string IPAddress { get; set; }
    public string HostName { get; set; }
    public string MACAddress { get; set; }
    public string Title { get; set; }
    public bool IsIGMonitor { get; set; }
    public string UpTime { get; set; }
    public DateTime DateEntered { get; set; }
    public string EnteredBy { get; set; }
    public Nullable<DateTime> DateUpdated { get; set; }
    public string UpdatedBy { get; set; }
    public ICollection<MachineRole> MachineRoles { get; set; }
    public ICollection<Role> Roles { get; set; }

Here's the linq statement I'm trying to use:

var query = (from m in unitOfWork.Context.Machines
            join u in unitOfWork.Context.Users
            on m.EnteredBy equals u.UserId into EntByUser
            from EnteredByUser in EntByUser.DefaultIfEmpty()
            join u2 in unitOfWork.Context.Users
            on m.UpdatedBy equals u2.UserId into UpdByUser
            from UpdatedByUser in UpdByUser.DefaultIfEmpty()
            join g in unitOfWork.Context.Groups
            on m.GroupId equals g.GroupId into Grp
            from Groups in Grp.DefaultIfEmpty()
            join s in unitOfWork.Context.SoftwareVersions
            on m.SoftwareVersionId equals s.SoftwareVersionId into SW
            from SoftwareVersions in SW.DefaultIfEmpty()
            join mr in unitOfWork.Context.MachineRoles
            on m.MachineId equals mr.MachineId into MachRoles
            from MachineRoles in MachRoles.DefaultIfEmpty()
            join r in unitOfWork.Context.Roles
            on MachineRoles.RoleId equals r.RoleId into Rolz
            from Rolz2 in Rolz.DefaultIfEmpty()
            select new ComputerModel()
                MachineId = m.MachineId,
                GroupId = m.GroupId,
                SoftwareVersionId = m.SoftwareVersionId,
                GroupName = Groups.GroupName,
                SoftwareVersion = SoftwareVersions.Version,
                IPAddress = m.IPAddress,
                HostName = m.HostName,
                MACAddress = m.MACAddress,
                Title = m.Title,
                IsIGMonitor = m.IsIGMonitor,
                UpTime = m.UpTime,
                DateEntered = m.DateEntered,
                DateUpdated = m.DateUpdated,
                EnteredBy = EnteredByUser.FirstName + " " + EnteredByUser.LastName,
                UpdatedBy = UpdatedByUser.FirstName + " " + UpdatedByUser.LastName,
                MachineRoles = m.MachineRoles,
                Roles = ?????

I can get MachineRoles to populate but I cannot get Roles to populate. I've tried Roles = Rolz2 but Rolz returns a single instance of Role, not a collection.

How can I get this query to return Machines and the related data for both MachineRoles and Roles?

I've looked at the following articles but haven't had much luck:
This SO Article
Loading Related Data - MSDN
Using Include with Entity Framework

I notice if I remove my model and use an anonymous type, then I don't get any errors:

select new ()
    GroupName = Groups.GroupName,

But this doesn't help me in my project because I need to use a Model for the data.

6/19/2018 6:33:45 PM

Popular Answer

If all the above tables are related via PK-FK relationship you can use linq lambda functions .Include() to include related tables and then use Navigation properties to access data.

If the tables are not related you can use LINQ left joins as shown in

It looks like you need a mix of inner and left joins. The above example and only achieve inner joins.

6/20/2018 2:49:11 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow