LINQ 2 SQL, Wrong SQL Generation

Question

Hi,

Have a DB table with the structure
Id (identity int), Company (string), Cluster (string), BU (string), Department (string),SalesPoint (string)

The following Linq Query:
var chEntities = from a in dataContext.CompanyHierarchies
let parent = (dataContext.CompanyHierarchies.Where(ch => ch.Id == companyHierarchyId).Single())
where
(
(a.Company == (parent.Company == null ? a.Company : parent.Company)) &&
(a.Cluster == (parent.Cluster == null ? a.Cluster : parent.Cluster)) &&
(a.Company == (parent.BU == null ? a.BU : parent.BU)) &&
(a.Company == (parent.Department == null ? a.Department : parent.Department)) &&
(a.Company == (parent.SalesPoint == null ? a.SalesPoint : parent.SalesPoint))
)
select new CompanyHierarchyEntity
{
Id = a.Id,
Name = a.SalesPoint == null ? (a.BU == null ? (a.Cluster == null ? (a.Company) : a.Cluster) : a.BU) : a.SalesPoint, CompanyHierarchyLevel = (CompanyHierarchyLevel)a.HierarchyLevel,
Company = a.Company,
Cluster = a.Cluster,
BU = a.BU,
Department = a.Department,
Section = a.SalesPoint
};

Generates the following SQL
{SELECT
(CASE
WHEN [t0].[SalesPoint] IS NULL THEN
(CASE
WHEN [t0].[BU] IS NULL THEN
(CASE
WHEN [t0].[Cluster] IS NULL THEN [t0].[Company]
ELSE [t0].[Cluster]
END)
ELSE [t0].[BU]
END)
ELSE [t0].[SalesPoint]
END) AS [Name], [t0].[Id], [t0].[HierarchyLevel] AS [CompanyHierarchyLevel], [t0].[Company], [t0].[Cluster], [t0].[BU], [t0].[Department], [t0].[SalesPoint] AS [Section] -------------Query Wrong After This, Company should be Comapny,Cluster, Bu, etc. but is Company in all cases FROM [dbo].[CompanyHierarchy] AS [t0]
WHERE ([t0].[Company] = (
(CASE
WHEN ((
SELECT [t1].[Company]
FROM [dbo].[CompanyHierarchy] AS [t1]
WHERE [t1].[Id] = @p0
)) IS NULL THEN [t0].[Company]
ELSE (
SELECT [t2].[Company]
FROM [dbo].[CompanyHierarchy] AS [t2]
WHERE [t2].[Id] = @p0
)
END))) AND ([t0].[Cluster] = (
(CASE
WHEN ((
SELECT [t3].[Cluster]
FROM [dbo].[CompanyHierarchy] AS [t3]
WHERE [t3].[Id] = @p0
)) IS NULL THEN [t0].[Cluster]
ELSE (
SELECT [t4].[Cluster]
FROM [dbo].[CompanyHierarchy] AS [t4]
WHERE [t4].[Id] = @p0
)
END))) AND ([t0].[Company] = (
(CASE
WHEN ((
SELECT [t5].[BU]
FROM [dbo].[CompanyHierarchy] AS [t5]
WHERE [t5].[Id] = @p0
)) IS NULL THEN [t0].[BU]
ELSE (
SELECT [t6].[BU]
FROM [dbo].[CompanyHierarchy] AS [t6]
WHERE [t6].[Id] = @p0
)
END))) AND ([t0].[Company] = (
(CASE
WHEN ((
SELECT [t7].[Department]
FROM [dbo].[CompanyHierarchy] AS [t7]
WHERE [t7].[Id] = @p0
)) IS NULL THEN [t0].[Department]
ELSE (
SELECT [t8].[Department]
FROM [dbo].[CompanyHierarchy] AS [t8]
WHERE [t8].[Id] = @p0
)
END))) AND ([t0].[Company] = (
(CASE
WHEN ((
SELECT [t9].[SalesPoint]
FROM [dbo].[CompanyHierarchy] AS [t9]
WHERE [t9].[Id] = @p0
)) IS NULL THEN [t0].[SalesPoint]
ELSE (
SELECT [t10].[SalesPoint]
FROM [dbo].[CompanyHierarchy] AS [t10]
WHERE [t10].[Id] = @p0
)
END)))
}

Has anyone else faced a siomilar issue? Kind regards,

Answer 1

I think this is your problem:

(a.Company == (parent.BU == null ? a.BU : parent.BU)) &&
(a.Company == (parent.Department == null ? a.Department : parent.Department)) &&
(a.Company == (parent.SalesPoint == null ? a.SalesPoint : parent.SalesPoint))

Shouldn't it be:

(a.BU == (parent.BU == null ? a.BU : parent.BU)) &&
(a.Department == (parent.Department == null ? a.Department : parent.Department)) &&
(a.SalesPoint == (parent.SalesPoint == null ? a.SalesPoint : parent.SalesPoint))

Answer 2

I rewrote your LINQ with the equivilant of a LEFT OUTER JOIN. I think something like this is what you're looking for. I can't really check the SQL generated as I don't have your DB.

var chEntities = from a in dataContext.CompanyHierarchies
join parent in (from ch in dataContext.CompanHeirarchies
       where ch.Id == companyHierarchyId
       select ch) on 
    new {a.Company, a.Cluster, a.BU, a.Department, a.SalesPoint} 
    equals new {parent.Company, parent.Cluster, parent.BU, parent.Department, parent.SalesPoint} 
    into temp
from t in temp.DefaultIfEmpty()
select new CompanyHierarchyEntity
{
Id = a.Id,
Name = a.SalesPoint == null ? (a.BU == null ? (a.Cluster == null ? (a.Company) : a.Cluster) : a.BU) : a.SalesPoint, 
CompanyHierarchyLevel = (CompanyHierarchyLevel)a.HierarchyLevel,
Company = a.Company,
Cluster = a.Cluster,
BU = a.BU,
Department = a.Department,
Section = a.SalesPoint
};

I guess you could add a .Single() after the Subquerie in the Join, but I'm not sure how that will affect your results.

Let me know if it works!