ITWorker ITWorker - 3 months ago 16
ASP.NET (C#) Question

How to use LINQ on a join table?

The query I am trying to replicate in LINQ is:

SELECT count(*) FROM joinTable WHERE object1ID = input_parameter1_from_code
AND object2ID = input_parameter2_from_code;


I have access to a IdentityDbContext, but it only contains references to the constituent objects' tables, not for the join table itself, so I don't know what to look for to try to get the result.

Alternatively, if I can just use this raw query, I would like to know how to do that as well.
Thank you.

Answer

I assume you have in mind many-to-many relationship with implicit "link" ("join", "junction") table. Something like this (most likely you are speaking for User and Role, but that's not essential):

public class One
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Two> Twos { get; set; }
}

public class Two
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<One> Ones { get; set; }
}

public class MyDbContext : DbContext
{
    public DbSet<One> Ones { get; set; }
    public DbSet<Two> Twos { get; set; }
}

Although you have no direct access to the link table, you can use either of the two "primary" tables combined with the navigation property of the other.

So, given

var db = new MyDbContext();

both

int count =
    (from one in db.Ones
     from two in one.Twos
     where one.Id == input_parameter1_from_code && two.Id == input_parameter2_from_code
     select new { one, two })
     .Count();

and

int count =
    (from two in db.Twos
     from one in two.Ones
     where one.Id == input_parameter1_from_code && two.Id == input_parameter2_from_code
     select new { one, two })
     .Count();

will produce identical SQL query similar to this:

SELECT
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT
        COUNT(1) AS [A1]
        FROM [dbo].[TwoOne] AS [Extent1]
        WHERE (1 = [Extent1].[One_Id]) AND (2 = [Extent1].[Two_Id])
    )  AS [GroupBy1]

which as you can see is against the link table.