Left Outer Join Results Difference Between EF Core 1.0 and EF 6.x

[rasikaw-is]

Hello everyone,

There is a difference in the way the results are generated in left outer joins between EF Core 1.0 and EF 6.x.

Results for EF Core 1.0 using same data and linq query:

Jim Warren : ,
William Greig : Town 1, 1000
Steve Jones : Town 2, 2000
Andrew Smith : Town 4, 4000
Andrew Smith : Town 4, 4000

Results for EF 6.x using same data and linq query:

Sam Tippet : Town 4, 4000
William Greig : Town 1, 1000
Steve Jones : Town 2, 2000
Jim Warren : ,
Andrew Smith : Town 4, 4000

Notice how 'Andrew Smith' duplicates in the EF Core 1.0 results. The default ordering is also different. I have attached screenshots of the two tables Customers and Postcodes. The code is below:

    public class CustomerListViewModel
    {
        public List<CustomerListItemViewModel> CustomersList { get; set; }
    }

    public class CustomerListItemViewModel
    {
        [Key,
         Display(Name = "Customer Id")]
        public int CustomerId { get; set; }

        [Display(Name = "Name")]
        public string CustomerName { get; set; }

        [Display(Name = "Town")]
        public string TownName { get; set; }

        [Display(Name = "Postcode")]
        public string PostcodeValue { get; set; }
    }

    // Linq EF query.
    var customers = from customer in _db.Customers
                    join postcode in _db.Postcodes
                        on customer.PostcodeID equals postcode.PostcodeID into custPCTmp
                    from custPC in custPCTmp.DefaultIfEmpty()
                    select new
                    {
                        CustomerID = customer.CustomerID,
                        CustomerName = customer.CustomerName,
                        TownName = (custPC == null ? string.Empty : custPC.TownName),
                        PostcodeValue = (custPC == null ? string.Empty : custPC.PostcodeValue)
                    };

    model.CustomersList = customers.Select(c => new CustomerListItemViewModel
    {
        CustomerId = c.CustomerID,
        CustomerName = c.CustomerName,
        TownName = c.TownName,
        PostcodeValue = c.PostcodeValue
    }).ToList();

    // View code.
    @model CustomerListViewModel

    @foreach (var item in Model.CustomersList)
    {
        <p>@item.CustomerName : @item.TownName, @item.PostcodeValue</p>
    }   

"Microsoft.EntityFrameworkCore.SqlServer": "1.0.0" (EF Core 1.0)
(EF 6.x)
Windows 10 Pro
Visual Studio 2015 Update 3 and NET Core 1.0 for Visual Studio - DotNetCore.1.0.0-VS2015Tools.Preview2.

[anpete]

@rasikaw-is Thanks for reporting. Are you able to post the SQL queries for both?

[rasikaw-is]

@anpete Sure, the database script for the tables and data is below (the same tables and data were used for both).

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Customers](
    [CustomerID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [varchar](120) NULL,
    [PostcodeID] [int] NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
    [CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Postcodes](
    [PostcodeID] [int] IDENTITY(1,1) NOT NULL,
    [PostcodeValue] [varchar](100) NOT NULL,
    [TownName] [varchar](255) NOT NULL,
 CONSTRAINT [PK_Postcode] PRIMARY KEY CLUSTERED 
(
    [PostcodeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Customers] ON 
GO
INSERT [dbo].[Customers] ([CustomerID], [CustomerName], [PostcodeID]) VALUES (1, N'Sam Tippet', 5)
GO
INSERT [dbo].[Customers] ([CustomerID], [CustomerName], [PostcodeID]) VALUES (2, N'William Greig', 2)
GO
INSERT [dbo].[Customers] ([CustomerID], [CustomerName], [PostcodeID]) VALUES (3, N'Steve Jones', 3)
GO
INSERT [dbo].[Customers] ([CustomerID], [CustomerName], [PostcodeID]) VALUES (4, N'Jim Warren', NULL)
GO
INSERT [dbo].[Customers] ([CustomerID], [CustomerName], [PostcodeID]) VALUES (5, N'Andrew Smith', 5)
GO
SET IDENTITY_INSERT [dbo].[Customers] OFF
GO
SET IDENTITY_INSERT [dbo].[Postcodes] ON 
GO
INSERT [dbo].[Postcodes] ([PostcodeID], [PostcodeValue], [TownName]) VALUES (2, N'1000', N'Town 1')
GO
INSERT [dbo].[Postcodes] ([PostcodeID], [PostcodeValue], [TownName]) VALUES (3, N'2000', N'Town 2')
GO
INSERT [dbo].[Postcodes] ([PostcodeID], [PostcodeValue], [TownName]) VALUES (4, N'3000', N'Town 3')
GO
INSERT [dbo].[Postcodes] ([PostcodeID], [PostcodeValue], [TownName]) VALUES (5, N'4000', N'Town 4')
GO
SET IDENTITY_INSERT [dbo].[Postcodes] OFF
GO

[anpete]

@rasikaw-is Thanks. Can you post the EF generated SQL queries, too? You can get them out of SQL Analyzer or by using EF's logging.

[rasikaw-is]

@anpete Yep, here they are. I have also included screenshots of the results (the first one is of EF Core 1.0 and the second one is of EF 6.x).

--EF Core 1.0
exec sp_executesql N'SELECT [postcode].[PostcodeID], [postcode].[PostcodeValue], [postcode].[TownName], [customer].[CustomerID], [customer].[CustomerName], @__Empty_0, @__Empty_1
FROM [Customer] AS [customer]
LEFT JOIN [Postcode] AS [postcode] ON [customer].[PostcodeID] = [postcode].[PostcodeID]
ORDER BY [customer].[PostcodeID]',N'@__Empty_0 nvarchar(4000),@__Empty_1 nvarchar(4000)',@__Empty_0=N'',@__Empty_1=N''

--EF 6.x
exec sp_executesql N'SELECT 
    [Extent1].[CustomerID] AS [CustomerID], 
    [Extent1].[CustomerName] AS [CustomerName], 
    CASE WHEN ([Extent2].[PostcodeID] IS NULL) THEN @p__linq__0 ELSE [Extent2].[TownName] END AS [C1], 
    CASE WHEN ([Extent2].[PostcodeID] IS NULL) THEN @p__linq__1 ELSE [Extent2].[PostcodeValue] END AS [C2] 
    FROM  [dbo].[Customer] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Postcode] AS [Extent2] ON [Extent1].[PostcodeID] = [Extent2].[PostcodeID]',N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)',@p__linq__0=N'',@p__linq__1=N''

[rasikaw-is]

@anpete Here is also the EF Core 1.0 debug view at the statement below (the additional fields were not included in the code for brevity):

    model.CustomersList = customers.Select(c => new CustomerListItemViewModel
    {
        CustomerId = c.CustomerID,
        CustomerName = c.CustomerName,
        TownName = c.TownName,
        PostcodeValue = c.PostcodeValue
    }).ToList();