Result of LINQ query is not equal to the result of a SQL query

Question

I have to make a SELECT over a handful of entities (tables) which needs a lot of JOINs.

When I write that query in SQL Server Management Studio, it works, but in my C# code with LINQ, I get no matches.

Here are a few examples:

SELECT 
    dbo.Person.PersonID,
    dbo.Person.txtFirstName,
    dbo.Person.txtLastName,
    dbo.Person.txtAccount
FROM
    [dbo].[Person]
INNER JOIN 
    dbo.CustomFieldData_Person ON dbo.Person.PersonID = dbo.CustomFieldData_Person.PersonID
INNER JOIN 
    dbo.CustomFieldData ON dbo.CustomFieldData_Person.CustomFieldDataID = dbo.CustomFieldData.CustomFieldDataID
INNER JOIN 
    dbo.CustomFieldListData ON dbo.CustomFieldData.CustomFieldDataID = dbo.CustomFieldListData.CustomFieldDataID
INNER JOIN 
    dbo.CustomListItem ON dbo.CustomFieldListData.CustomListItemID = dbo.CustomListItem.CustomListItemID
WHERE 
    [dbo].[CustomListItem].[CustomListItemID] = 218
    AND [dbo].[CustomListItem].[CustomListID] = 41
    AND [dbo].[Person].[InstanceID] = 80

Matches: 30

List<Person> result =
        (from p in DB_Instance_Singleton.getInstance.Person
         join cfdp in DB_Instance_Singleton.getInstance.CustomFieldData_Person on p.PersonID equals cfdp.PersonID
         join cfd in DB_Instance_Singleton.getInstance.CustomFieldData on cfdp.CustomFieldDataID equals cfd.CustomFieldDataID
         join cfld in DB_Instance_Singleton.getInstance.CustomFieldListData on cfd.CustomFieldDataID equals cfld.CustomFieldDataID
         join cli in DB_Instance_Singleton.getInstance.CustomListItem on cfld.CustomListItemID equals cli.CustomListItemID
         where (cli.CustomListItemID == iCustomListItemID)
         && (cli.CustomListID == iCustomListID)
         && (p.InstanceID == iInstanceID)
         select p).ToList<Person>();

Matches: 0

SELECT dbo.Person.PersonID,
       dbo.Person.txtFirstName,
       dbo.Person.txtLastName,
       dbo.Person.txtAccount
FROM [dbo].[Person]
INNER JOIN dbo.CustomFieldData_Person ON dbo.Person.PersonID = dbo.CustomFieldData_Person.PersonID
INNER JOIN dbo.CustomFieldData ON dbo.CustomFieldData_Person.CustomFieldDataID = dbo.CustomFieldData.CustomFieldDataID
INNER JOIN dbo.CustomFieldListData ON dbo.CustomFieldData.CustomFieldDataID = dbo.CustomFieldListData.CustomFieldDataID
INNER JOIN dbo.CustomListItem ON dbo.CustomFieldListData.CustomListItemID = dbo.CustomListItem.CustomListItemID
WHERE [dbo].[Person].[InstanceID] = 80

Matches: 142

    List<Person> result =
        (from p in DB_Instance_Singleton.getInstance.Person
         join cfdp in DB_Instance_Singleton.getInstance.CustomFieldData_Person on p.PersonID equals cfdp.PersonID
         join cfd in DB_Instance_Singleton.getInstance.CustomFieldData on cfdp.CustomFieldDataID equals cfd.CustomFieldDataID
         join cfld in DB_Instance_Singleton.getInstance.CustomFieldListData on cfd.CustomFieldDataID equals cfld.CustomFieldDataID
         join cli in DB_Instance_Singleton.getInstance.CustomListItem on cfld.CustomListItemID equals cli.CustomListItemID
         where (p.InstanceID == iInstanceID)
         select p).ToList<Person>();

Matches: 142

It seems that I have a problem with the columns from the entity CustomListItem in the WHERE clause.

But I don't understand why.

Use SQL Profiler and show us a sql generated by Entity Framework. — pwas, Jun 30 '16 at 15:17
@nopeflow You can use profiler, but EF already have a function to get the final query. http://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework — Juan Carlos Oropeza, Jun 30 '16 at 15:21
you hard coded the param values and still get different results? — JamieD77, Jun 30 '16 at 15:31
@JamieD77: Thanks a lot! You are right. With hardcoded values it works, but i don't understand why. I have to guess, i'm new to LINQ-queries. — Patrick Pirzer, Jul 01 '16 at 06:40
Finally i have found my mistake. In the C#-code i swapped in the heat of the night two parameters :) If wanna hear the right song to the moment, listen to Glen Campbell "Guess i'm dumb". — Patrick Pirzer, Jul 01 '16 at 07:08

score 0 · Accepted Answer · answered Jul 04 '16 at 06:32

0

Thanks to JamieD77 i have found my mistake. The LINQ-SQL is correct but i in my C#-code i swapped two parameters.

answered Jul 04 '16 at 06:32

Patrick Pirzer

1,649
3
22
49

Result of LINQ query is not equal to the result of a SQL query

1 Answers1