EF core join using include

Question

I am trying to join related data using Include but I am having some difficulties. My models are the following

public partial class GBTObject
{
    public uint Id { get; set; }
    public uint OrganizationId { get; set; }
    public string Name { get; set; }
    public virtual Device Device { get; set; }
    public uint? DeviceId { get; set; }
}

public partial class Device
{
    public uint Id { get; set; }
    public uint OrganizationId { get; set; }
    public string UUID { get; set; }
    public bool? Enabled { get; set; }
}

public partial class DeviceState
{
    public uint Id { get; set; }
    public uint OrganizationId { get; set; }

    public uint DeviceId { get; set; }
    public string State { get; set; }
    public DateTime? Timestamp { get; set; }
    public byte? Event { get; set; }
    public ulong TotalDistance { get; set; }
    public string UserAgent { get; set; }
}

var data = _context.GBTObject
    .Where(x => x.DeviceId != null && x.OrganizationId == _user.OrganizationId)
    .Include(x => x.Device)
    .Include(x => x.State)

Then I tried to create a shadow property inside Device

[ForeignKey("Id")] public virtual DeviceState State{ get; set; }

var data = _context.GBTObject
    .Where(x => x.DeviceId != null && x.OrganizationId == _user.OrganizationId)
    .Include(x => x.Device)
    .ThenInclude(x => x.State)

But it doesn't work cause the it joins using the the DeviceId from GBTObject with Id from DeviceState. Changing the foreign key to DeviceId results in weird naming errors(it names the GBTObject.DeviceId to GBTObject.DeviceId1 and then it complains that it doesn't exist and looks like a bug).

Am I doing this wrong?

What type of relationship between `Device` and `DeviceState` are you trying to create? With the original models, it would be one-to-many. — Ivan Stoev, May 11 '18 at 08:29

score 0 · Answer 1 · answered May 11 '18 at 07:20

0

Try the following:

var data = from gbt in _context.GBTObject
               join ds in _context.DeviceState
               on gbt.DeviceId equals ds.DeviceId
               where gbt.DeviceId != null && gbt.OrganizationId == _user.OrganizationId
               select gbt;

Also check this link for further info about joining in LINQ:

What is the syntax for an inner join in LINQ to SQL?

answered May 11 '18 at 07:20

G43beli

3,835
4
21
28

`Include` and `ThenIclude` are producing left joins. In this case I need a left join. Also if this work correctly it would help to make this kind of joins more concise and clear. – kechap May 11 '18 at 07:26
`join [...] on` is also left join. See here: http://www.devcurry.com/2011/01/linq-left-join-example-in-c.html – G43beli May 11 '18 at 07:32
I know that you can use join for left joins but the syntax gets complicated if you join 2,3 tables. As I am rewriting some code I want to avoid this syntax. – kechap May 11 '18 at 07:41

EF core join using include

1 Answers1