Efcore: .Include() .Count() not using inner join for count
I'm using Sqlserver 2008( old database, without foreight key relation )， and recently I try to using EntityFramework Core to access the database .
```C#
public class A
{
public int Id { get; set; }
public string Name { get; set; }
}
public class B
{
public int Id { get; set; }
public string Title { get; set; }
public int AId{ get; set; } // database may have a int value but may not actually exists
public A A{ get; set; }
}
```
.Count() and .Select()' or 'Take() produce very different sql when using Include(x=>x.property) .
.Select()' or 'Take() will use Inner Join for navigation property, but .Count() will just igone them.
And that mean when I paging the data , I will get wrong pagecount
Does this be a bug, Or I had to use int? for the model and force to use .Join() to produce Inner Join opration
Further technical details
EF Core version: 1.0.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: windows 7 x64
IDE: Visual Studio 2015
John0King
All 12 comments
Regardless of the database version, EF Core treats AId as a foreign key. The fact that its int and not int? makes it a required relationship and causes the INNER JOINs to be generated. If you want LEFT JOINs then you should use int?
maumar
on 18 Apr 2017
@maumar I do want to use INNER JOIN but It not work with .Count() Method.
C#
var query = db.Set<B>().Include(b=>b.A);
var list = query.ToList(); // 2 row : SELECT * FROM B INNER JOIN A on B.AId = A.Id
var count = query.Count(); // return 5 : SELECT count(*) FROM B
John0King
on 18 Apr 2017
@John0King - What you are seeing is optimized query specifically ignored include. When you do Select or Skip or Take then you are projecting out the B in final results. Due to eagerly loading via include, The navigation property B.A needs to be populated. When you do Count you are not projecting out B therefore there are no navigation property to load hence Inner Join is not required. More on ignored include here https://docs.microsoft.com/en-us/ef/core/querying/related-data#ignored-includes
I do want to use INNER JOIN but It not work with .Count() Method
Based on your model definition, there is a relationship between B & A. Due to existence of B.AId which matches convention rules of FK property name, EF will create a relationship with B.AId as a FK which points to A.Id. Since the type of B.AId is int (non-nullable) that means that every B will have FK set. Further since it is dependent end (containing FK property), it also imply that every value of B.AId will be present in A.Id. It implies that for every row in B there is 1 matching row in A. So count of rows in B is same is count of rows after inner join with A. Hence the optimized query will just count number of rows in B. (If for some reason the query needed Left outer join then we still need to perform the join to find the count because it may not be same as table row count).
In EF, (even with previous versions) existence of navigation property implied relationship between table. In you case, if database doesn't have foreign key constraint then you may need to remove navigation and work with FK property manually. More details on relationships in EF here https://docs.microsoft.com/en-us/ef/core/modeling/relationships
Hope that answers all your questions.
smitpatel
on 18 Apr 2017
@smitpatel That make sense for a strong relation database .
And my personal opinion is : optimized query should detect is the .Include() perform a INNER JOIN , if not then it's safe to ignore them , if true then do not ignore , even when use .Select but not take a value in navigation property.
John0King
on 19 Apr 2017
There are multiple facets of problem here.
Include is EF specific API which tells EF to load navigation property when creating object of mapped entity type. In reference navigation case it creates such a simple join. For collection navigations, it creates multiple queries to support streaming. Include by no means a pointer to tell EF to perform a join. If final projection is not entity type whose navigations are eagerly loaded, Include will be ignored.
Your database does not have foreign key constraint enforced, but you have reference navigation. The EF metadata model will enforce a strong relationship. It certain works with the database as database is more relaxing than EF. But due to stronger semantics of relationship in model, you cannot rely on EF model to do navigation expansions for you and create appropriate join.
To give simple example,
If you define a nullable FK property, but your database does not allow null in the column, then from database perspective you need Inner Join but EF will still generate a left outer join.
The best solution in your case is instead of relying on EF to produce join as you expect, hand-craft join query.
Following should give you what you want. 😄
```C#
var query = (from b in db.Set()
join a in db.Set()
on b.AId equals a.Id
select b).Count();
```SQL
SELECT COUNT(*)
FROM [Bs] AS [b]
INNER JOIN [A] AS [a] ON [b].[AId] = [a].[Id]
:( Current I manually do a join like you mentioned 。
I had to say Include is the most easy way to do a Join (and most stable api for doing a join in database today)
If you define a nullable FK property, but your database does not allow null in the column, then from database perspective you need Inner Join but EF will still generate a left outer join.
nullable tell EF to use LEFT OUTER JOIN and ValueType or required tell EF to use INNER JOIN .
That's the reason I use int instead of int? .
And my personal opinion is : optimized query should detect is the .Include() perform a INNER JOIN , if not then it's safe to ignore them , if true then do not ignore , even when use .Select but not take a value in navigation property.
I don't know how EF process those thing. And I think it will be better if it can detect the FK is non-nullable and not to ignore them .
I will close this issue, but may be you should confirm that does it have same problem when you use NO-SQL database like mongodb
John0King
on 19 Apr 2017
@John0King EF relies on a well-formed and consistent mapping from EF model to database model when using a relational database, as was explained by @smitpatel Any non-relational provider will have different requirements based on the semantics of that back-end.
ajcvickers
on 19 Apr 2017
@ajcvickers there many developers argument about the Foreign Key constraints in https://stackoverflow.com/questions/83147/whats-wrong-with-foreign-keys
and I think if we already config the relation in our code first model , let EF dealing with it should be possible ( delete, update , validate ).
John0King
on 19 Oct 2017
@John0King ,
Instead of using like previous:
var query = db.Set().Include(b=>b.A);
var list = query.ToList(); // 2 row : SELECT * FROM B INNER JOIN A on B.AId = A.Id
var count = query.Count(); // return 5 : SELECT count(*) FROM B
here is what I found might work:
var Current_B = this._context.B.Single(b => b.Id == Id);
return await this._context.Entry(Current_B )
.Collection(b=> b.A)
.Query()
.CountAsync();
But I am really looking forward that the B.Include.Count() should work in counting items in joined query and not do another tricky count from only table B in EF Core....
Hope that helps!
kevinding0218
on 14 Jan 2018
@kevinding0218 that does not work for my case. Previously I do not want use join is because EF 1.x.x is not stable for complex query. and 2.x is much better now .
and thanks anyway .
John0King
on 15 Jan 2018
In E.F 2.2 there is a problem when using a EF Core GlobalFilter like softdelete.
If the table joined has a softdelete filter, the count does not generates the inner and only counts the primary table so the count returns a wrong result as is not taking into account the filtered records on the inner.
Sample : A -> 5 records, B -> 5 records matching (but one with isDeleted set by a global filter) Count returns 5 instead of 4 because Count only returns a query over A.
But if we execute the query (no counting) it correctly returns the records joinin with B where isdeleted=false
ppcar007
on 9 Jul 2020
Did you find any solution for this? I'm using ef core 3.1 and I'm having the same problem here
lopesrichard
on 14 Aug 2020
Related issues
ra0o0f
·
3Comments
mohsin91
·
3Comments
MontyGvMC
·
3Comments
ryanwinter
·
3Comments
HeMinzhang
·
3Comments
Most helpful comment
@maumar I do want to use INNER JOIN but It not work with
.Count()Method.C# var query = db.Set<B>().Include(b=>b.A); var list = query.ToList(); // 2 row : SELECT * FROM B INNER JOIN A on B.AId = A.Id var count = query.Count(); // return 5 : SELECT count(*) FROM B