Linq to Sql Count Include joins

Question

I have a Linq to sql as following:

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

Now due to some bad data all contacts in my members don't have corresponding Contact record. So during the count how can i include the Count after Inner Join with the Contact table.

The problem is when i get the list of members, the list of members has 100 Records while Count has 150 records (50 records being 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 Resulting Query during count, apparently it doesn't do JOIN with Contact table while Count()

Update Database structure

Member Table
Id   ContactId, CompanyId, CreatedDate ...

Contact Table
Id   Name ...

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

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

The bad data goes like this

I had previously 1,2,3,4,5,6,7,8,9,10 as Contact records and all those contacts were in Member table too.

Now I deleted records from Contact table lets say 6-10. But happened not to delete from Member table.

So this is causing an issue with the count. Yah sure, deleting the bad data from Member solves the issue but the question is how to use join while using Count().

Note: I use database initializer null

Update 2 I used LinqPad and Tried both default Linq To SQL and EntityFramework (DbContext) connection and what i found is confusing.

For Query:

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

Using Default Linq To SQL

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.. don't know what to do here . btw: Sorry for having a tag with linq-to-sql it is actually entityframework

Answer 1

What 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 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 could also try this:

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 right but, are the records containing bad data, duplicates? If yes, then why not use distinct() to only get those 100 good records? If all you want is just the count from the list, then why not use:

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