Why does Entity Framework 5 query different tables when executing .ToList () versus .Count () on the same object?

Question

I use the Entity Framework to map two tables together, using Entity Splitting, as described here and here .

I found that if I executed .ToList() on an IQueryable<SplitEntity> , then the results would be obtained from Inner Join. However, if I take the same IQueryable and run .Count() , it will return the number of records returned by Full Join.

Here is the unit test that fails:

  [TestMethod] public void GetCustomerListTest() { // arrange List<Customer> results; int count; // act using (var context = new DataContext()) { results = context.Customers.ToList(); count = context.Customers.Count(); } // assert Assert.IsNotNull(results); // succeeds Assert.IsTrue(results.Count > 0); // succeeds. Has correct records from inner join Assert.AreEqual(count, results.Count); // This line fails. Has incorrect count from full join. } 

It looks very bad. How can I get the .Count() method to return the results from Inner Join, like .ToList() ?

Update - SQL

I was mistaken about complete and internal associations.

.ToList () results in:

  SELECT [Extent1].[CustomerNumber] AS [CustomerNumber], -- ...etc... [Extent2].[CustomerName] AS [CustomerName], -- ... etc... FROM [dbo].[CustomerTable1] AS [Extent1] INNER JOIN [dbo].[CustomerTable2] AS [Extent2] ON [Extent1].[CustomerNumber] = [Extent2].[CustomerNumber] 

The result of .Count () results in:

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

Update - DataContext code and object code

DataContext:

 public class DataContext : DbContext { public DataContext() { Database.SetInitializer<DataContext>(null); } public DbSet<Customer> Customers { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Configurations.Add(new CustomerMapping()); } } } 

Customer Mapping (FluentAPI):

 public class CustomerMapping : EntityTypeConfiguration<Customer> { public CustomerMapping() { this.Map( m => { m.Properties( x => new { x.CustomerNumber, /*...etc...*/}); m.ToTable("CustomerTable1"); }) .Map( m => { m.Properties( x => new { x.CustomerName, /*...etc...*/}); m.ToTable("CustomerTable2"); }); } } 

Client Object:

 public class Customer { [Key] public string CustomerNumber { get; set; } public string CustomerName { get; set; } } 

Answer 1

If the database and all records in CustomerTable1 and CustomerTable2 were created using the Entity Framework and SaveChanges calls in the application code, this difference should not occur, and you can go straight ahead and report it as an error .

If you are tied to an existing database or if other applications write records to tables, and you really expect that not every record in CustomerTable1 has a corresponding record in CustomerTable2 , and vice versa, then entity separation is not a proper display of your database schema.

Apparently, the difference means that you can have Customer with CustomerNumber (etc.), but without CustomerName (etc.) - or vice versa. The best way to model this would be a one-to-one relationship where one side is required and the other side is optional. To do this, you need an additional object and a navigation property, for example:

 [Table("CustomerTable1")] public class Customer { [Key] public string CustomerNumber { get; set; } // + other properties belonging to CustomerTable1 public AdditionalCustomerData AdditionalCustomerData { get; set; } } [Table("CustomerTable2")] public class AdditionalCustomerData { [Key] public string CustomerNumber { get; set; } public string CustomerName { get; set; } // + other properties belonging to CustomerTable2 } 

With this mapping of the Fluent API:

 public class CustomerMapping : EntityTypeConfiguration<Customer> { public CustomerMapping() { this.HasOptional(c => c.AdditionalCustomerData) .WithRequired() .WillCascadeOnDelete(true); } } 

Answer 2

I query the local table and I get the same score for both. I believe that there is a problem with your context and why your results are inconsistent.

a screenshot of essentially the same code that just asks for a simple data set.

UPDATE:

I do not know why the SQL that is generated is different. You might think that they would be the same, except for just doing Count (*) instead of returning all the rows. It’s clear why you get different bills. I just can't say why SQL is different.

Perhaps John Skeet or another genius will see this and answer! :)