1

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.

ITWorker
  • 965
  • 2
  • 16
  • 39

5 Answers5

3

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.

Ivan Stoev
  • 195,425
  • 15
  • 312
  • 343
  • Yes it is a many-to-many relationship table that was autogenerated from the model definitions. This is exactly what I was looking for. Thanks! – ITWorker Aug 15 '16 at 16:20
2

In query syntax:

var amount = (from record in DBcontext.joinTable
              where record.object1ID = input_parameter1_from_code &&
                    record.object2ID = input_parameter2_from_code
              select record).Count();

In Method syntax:

var amount = DBcontext.joinTable
                      .Where(record => record.object1ID = input_parameter1_from_code &&
                                       record.object2ID = input_parameter2_from_code)
                      .Count();
Gilad Green
  • 36,708
  • 7
  • 61
  • 95
  • I made a mistake in my question (now fixed) saying I had a DBcontext, but it is actually an IdentityDbContext. Additionally, I get an error saying the db context does not contain a definition for joinTable. Should joinTable be in some kind of string since it is a db table name? – ITWorker Aug 15 '16 at 15:41
2

You can use Database.SqlQuery method which accepts raw sql query along with the parameters that you need to use with your query and advantage of using sql parameter is to avoid sql injection.

Try like this:

var data = yourContext.Database.SqlQuery<int>(
    "SELECT count(*) FROM joinTable WHERE object1ID = @code1 AND object2ID = @code2",
    new SqlParameter("@code1", input_parameter1_from_code),
    new SqlParameter("@code2", input_parameter2_from_code)
);

Let me know if this didnt work for you :)

Ivan Stoev
  • 195,425
  • 15
  • 312
  • 343
I Love Stackoverflow
  • 6,738
  • 20
  • 97
  • 216
  • Thanks, a variation of this worked for another query. I had to replace the `yourContext.Database.SqlQuery` to `yourContext.YourObject.SqlQuery` where YourObject is a model, and added a `.ToList()` before the `;' at the end. This was to return a list of objects, not just a count. – ITWorker Aug 16 '16 at 20:13
1

You can definitely use that query with a DbContext. Take a look at the MSDN documentation over here:

https://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.executequery(v=vs.110).aspx

It will be something like:

var Count = DbContext.ExecuteQuery("SELECT count(*) FROM joinTable where object1ID = input_parameter1_from_code 
AND object2ID = input_parameter2_from_code;");
ProgrammerV5
  • 1,915
  • 2
  • 12
  • 22
  • I am trying this with IdentityDbContext so I had to change the query to `var Count = db.Database.ExecuteSqlCommand("EXEC SELECT count(*) FROM dbo.AppRoleAppPrivileges where AppRole_Id = "+roleId+" AND AppPrivilege_ID = " +privId+"; ");`, but now I get a Syntax error in the debugger, even without the `EXEC` – ITWorker Aug 15 '16 at 15:52
  • db.Database.ExecuteS‌​qlCommand("SELECT count(*) FROM dbo.AppRoleAppPrivil‌​eges where AppRole_Id = "+roleId+" AND AppPrivilege_ID = " +privId+"; "); – ProgrammerV5 Aug 15 '16 at 15:54
  • 1
    No, please don't do this, make sure you used a parameterized query. see [How to pass parameters to the DbContext.Database.ExecuteSqlCommand method?](http://stackoverflow.com/questions/5474264/how-to-pass-parameters-to-the-dbcontext-database-executesqlcommand-method) – Bob Vale Aug 15 '16 at 16:12
  • Bob's right. It is definitely worth it to code right, it could be easier now but in the long run this code will cost you. – ProgrammerV5 Aug 15 '16 at 16:23
  • I will keep that in mind for the future when I use raw queries. Thank you both. – ITWorker Aug 15 '16 at 16:39
0

This should work, even in case of link table

dbContext.CollectionOne.where(x => x.Id ==  1).SelectMany(x => x.Collection2).where(y => y.Id == 2).Count()
krishnaprasad parthasarathy
  • 13
  • 6