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.
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.
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();
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 :)
yourContext.Database.SqlQuery<int> 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.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;");
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
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()
joinTable? is it a table in your database? a view?