Linq to Sql Count Enables joins

Question

I have Linq to sql as shown below:

var members=db.Members.Include(x=> x.Contact).Count(); 

Now, due to some bad data, all contacts in my members do not have the corresponding Contact record. So, during the count, how can I include the graph after Inner Join in the contact table.

The problem is when I get the member list, the member list has 100 entries , and Count has 150 entries (50 entries are bad data) .

 var membersQ=db.Members.Include(x=> x.Contact).Select(i=> new MemberViewModel(){ Name = i.Contact.Name, ContactId= i.Contact.Id, CreatedDate= i.CreatedDate }).AsQueryable(); var members=memberQ.ToList();// =100,paging is done... // the memebers list uses paging but the count doesn't var total=membersQ.Count(); // =150 

I checked the final query while counting, apparently it doesn’t do JOIN with the contact table, but Count()

Database structure update

 Member Table Id ContactId, CompanyId, CreatedDate ... Contact Table Id Name ... 

The foreign key for ContactId in the Member table is not set at the database level, but only on the model.

 [ForeignKey("ContactId")] Public Contact Contact { get; set; } 

Bad data looks like

I used to have 1,2,3,4,5,6,7,8,9,10 as Contact entries, and all these contacts were also in the Member table.

Now I deleted the entries from the contact table, say, 6-10. But it was not deleted from the Member table.

So this causes a problem with the counter. I'm sure deleting bad data from Member solves the problem, but the question is how to use join while using Count() .

Note. I am using a null database initializer

Update 2 I used LinqPad and tried both the default Linq To SQL and EntityFramework (DbContext), and what I found is confusing.

For request:

 (from a in Members join b in Contacts on a.ContactId equals b.ContactId select a).Count() 

Using Linq for SQL by default

 SELECT COUNT(*) AS [value] FROM [Member] AS [t0] INNER JOIN [Contact] AS [t1] ON [t0].[ContactID] = [t1].[ContactID] 

Using Entityframework DbContext

 SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT(1) AS [A1] FROM [dbo].[Member] AS [Extent1] ) AS [GroupBy1] 

In my code, I use the DbContext method. So ... I don't know what to do here, btw: Sorry for the presence of the linq-to-sql tag, actually EntityFramework

Answer 1

How about this:

 var x = from m in Members join c in Contacts on m.ContactId equals c.ID select new { Name = c.Name, ContactId= c.ID, CreatedDate= c.CreatedDate }; Console.Write(x.Count()); 

EDIT

When I use LinqPad with this query and look at the generated SQL, I get:

 SELECT COUNT(*) AS [value] FROM [Members] AS [t0] INNER JOIN [Contact] AS [t1] ON [t0].[ContactId] = ([t1].[ID]) 

EDIT 2

You can also try the following:

 var x = from c in Contacts from m in Members where m.ContactId == c.ID select new { Name = c.Name, ContactId= c.ID, CreatedDate= c.CreatedDate }; Console.Write(x.Count()); 

Answer 2

Not sure if this is correct, but are records containing bad data duplicates? If so, why not use separate () to get only 100 good entries? If all you need is just an account from the list, then why not use:

 var members = memberQ.ToList(); int total = members.Count; 

Answer 3

The answer to this is that you simply do not have a connection between members and a contact. The member table has 150 rows and the contact table has 100 rows. Therefore, when you try to retrieve, you actually concatenate the data row by row until some table ends. When you think you are counting the first table.

Solution . You need to add FK for contacts from members or any type of identifier with which you can "bind" data to members from a contact.

More explanation and even more comments.

The Members table has a link to Contact . which you populate with your Include(x=> x.Contact) statement Include(x=> x.Contact)

So when you try to do it

 new MemberViewModel(){ Name = i.Contact.Name, ContactId= i.Contact.Id, CreatedDate= i.CreatedDate } 

You are requesting contact content (as you probably know).

Now for some explanation. When you query the database, as you do in MembersQ , you do not retrieve / implement the data. (you are just preparing a request)

To execute the request, you must call ToArray() , ToList() , etc. And when you do foreach on data, you implement one row at a time (cursor).

Now for your result, when you call ToList (), you implement a list of List types (now only elements that can be filled are used (this is your 100))

When you execute memberQ.Count() , you request a query counter from the database, since the database does not know anything about MemberViewModel , it will return the counters corresponding to the possible Where(x=>{filter}) (this is your 150)

A simple fix is ​​to write ToList() instead of AsQueryable() . I'm a little rusty on LINQ-to-SQL, you might have to do AsQueryable().ToList()

A possible solution is to pre-filter.

 var membersQ=db.Members.Include(x=> x.Contact).Where(a=> a.Contact != null).Select(i=> new MemberViewModel(){ Name = i.Contact.Name, ContactId= i.Contact.Id, CreatedDate= i.CreatedDate }).AsQueryable(); 

now membersQ.Count() should be equal to var membersQ.ToList().Count()

And to do the paging, use memberQ.Skip(x).Take(y) , this will be done in SQL, not on the web server. If you are not ToList() , then everything is on the server, when suddenly a data set is in the server’s memory.

If this does not work , then you do not have the foreigh key present, you can simply change the filter to filter in ContactID instead of Contact. add FK (foreign key)

Add foreign key:. If you have access to modify your SQL, then make the ContactID in Member NULL and add the foreign key from the ContactID and Contact ID members.

Then remove the two tables from your dbml and drag them again from db explorer. Now you should be able to simply use the code without including, as it will be "Lazy loaded" (which means "no") compared to the loaded Eager (which means you have to say what to load)

Answer 4

try it

 var members=db.Members.Include(x=> x.Contact) .Where(x => x.Contact != null) .Count(); 

Answer 5

 var members = db.Contracts.Where(w=> w.Member != null).Select(i=> new MemberViewModel(){ Name = i.Contact.Name, ContactId= i.Contact.Id, CreatedDate= i.CreatedDate }).AsQueryable()); 

Is this what you do?