none
Linq to Entities .ToList<>() results are not the same as the Linq SQL results RRS feed

  • Question

  • I have a linq query that I sent to the Debug window then ran the unmodified query in SQL Management Studio.  The results are what are expected, but when I execute ToList<>() on that query the objects in the list do not have the values expected. 

    In the below linq dv and vFormulaSelect are entity objects and cmps is a list<string>

    var vfsl = (from f in vdb.vFormulaSelects
    where (f.SUPPID.Equals(dv.SUPPID) || (dv.SUPPID.Equals(null) && f.SUPPID.Equals(null)))
        && f.TYPCD == dv.DONTYP
        && (from cf in vdb.vFormulaSelects
            where (cmps.Contains(cf.COMPCD) && cf.ProductStage == "F")
            select cf.SETID).Contains(f.SETID)
        && ((from ci in vdb.vFormulaSelects
            where cmps.Contains(ci.COMPCD) && ci.ProductStage == "I"
             select ci.SETID).Contains(f.SETID)
                    || !vdb.ComponentCodes.Any(ps => ps.ProductStage == "I" && ps.SETID == f.SETID))
    select f);

    string sql = ((System.Data.Entity.Infrastructure.DbQuery<vFormulaSelect>)vfsl).ToString();
    System.Diagnostics.Debug.WriteLine(sql);

    fsl = vfsl.ToList<vFormulaSelect>();

    Copying the result from the VS Debug window into SQL Management and setting the parameters:

    declare @p__linq__0 nvarchar(10);
    declare @p__linq__1 nvarchar(10);
    declare @p__linq__2 nvarchar(2);
    
    set @p__linq__0 = 'TR-700';
    set @p__linq__1 = @p__linq__0;
    set @p__linq__2 = 'XR';
    
    SELECT 
    [Extent1].[SETID] AS [SETID],
    [Extent1].[Name] AS [Name],
    [Extent1].[SUPPID] AS [SUPPID],
    [Extent1].[TYPCD] AS [TYPCD],
    [Extent1].[COMPCD] AS [COMPCD],
    [Extent1].[ProductStage] AS [ProductStage]
    FROM (SELECT
          [vFormulaSelect].[SETID] AS [SETID],
          [vFormulaSelect].[Name] AS [Name],
          [vFormulaSelect].[SUPPID] AS [SUPPID],
          [vFormulaSelect].[TYPCD] AS [TYPCD],
          [vFormulaSelect].[COMPCD] AS [COMPCD],
          [vFormulaSelect].[ProductStage] AS [ProductStage]
          FROM [dbo].[vFormulaSelect] AS [vFormulaSelect]) AS [Extent1]
    WHERE (([Extent1].[SUPPID] = @p__linq__0) OR ((@p__linq__1 IS NULL) AND ([Extent1].[SUPPID] IS NULL))) AND ([Extent1].[TYPCD] = @p__linq__2) AND ( EXISTS (SELECT
        1 AS [C1]
        FROM (SELECT
          [vFormulaSelect].[SETID] AS [SETID],
          [vFormulaSelect].[Name] AS [Name],
          [vFormulaSelect].[SUPPID] AS [SUPPID],
          [vFormulaSelect].[TYPCD] AS [TYPCD],
          [vFormulaSelect].[COMPCD] AS [COMPCD],
          [vFormulaSelect].[ProductStage] AS [ProductStage]
          FROM [dbo].[vFormulaSelect] AS [vFormulaSelect]) AS [Extent2]
        WHERE ([Extent2].[COMPCD] IN (N'76057',N'76087',N'76088',N'78524',N'78531',N'MBAG')) AND (N'F' = [Extent2].[ProductStage]) AND ([Extent2].[SETID] = [Extent1].[SETID])
    )) AND (( EXISTS (SELECT
        1 AS [C1]
        FROM (SELECT
          [vFormulaSelect].[SETID] AS [SETID],
          [vFormulaSelect].[Name] AS [Name],
          [vFormulaSelect].[SUPPID] AS [SUPPID],
          [vFormulaSelect].[TYPCD] AS [TYPCD],
          [vFormulaSelect].[COMPCD] AS [COMPCD],
          [vFormulaSelect].[ProductStage] AS [ProductStage]
          FROM [dbo].[vFormulaSelect] AS [vFormulaSelect]) AS [Extent3]
        WHERE ([Extent3].[COMPCD] IN (N'76057',N'76087',N'76088',N'78524',N'78531',N'MBAG')) AND (N'I' = [Extent3].[ProductStage]) AND ([Extent3].[SETID] = [Extent1].[SETID])
    )) OR ( NOT EXISTS (SELECT
        1 AS [C1]
        FROM [dbo].[ComponentCode] AS [Extent4]
        WHERE (N'I' = [Extent4].[ProductStage]) AND ([Extent4].[SETID] = [Extent1].[SETID])
    )))
    ;


    Which gives these results:

    5    Model Airplanes    TR-700    XR    MBAG    I
    5    Model Airplanes    TR-700    XR    78643    F
    5    Model Airplanes    TR-700    XR    78643B    F
    5    Model Airplanes    TR-700    XR    76077    F
    5    Model Airplanes    TR-700    XR    76077A    F
    5    Model Airplanes    TR-700    XR    76088    F
    5    Model Airplanes    TR-700    XR    76089    F

    But, if I examine the list fsl = vfsl.ToList<vFormulaSelect>(); or put it out in the view it contains this:

    5     Model Airplanes     TR-700     XR     MBAG     I
    5     Model Airplanes     TR-700     XR     MBAG     I
    5     Model Airplanes     TR-700     XR     MBAG     I
    5     Model Airplanes     TR-700     XR     MBAG     I
    5     Model Airplanes     TR-700     XR     MBAG     I
    5     Model Airplanes     TR-700     XR     MBAG     I
    5     Model Airplanes     TR-700     XR     MBAG     I

    Which are incorrect.  Somehow the first record is echoed. 

    I can't find any information about this and can't see why it's happening.  Does anyone have suggestions?

    The project is in VS2012, MVC4, EntityFramework.dll is v4.0.30319


    Friday, July 11, 2014 11:38 PM

All replies

  • The correct sql is generated by the framework, and the correct results come back when that sql is executed in the management studio. 

    The correct results are not returned by the (linq query).ToList<vFormulaSelect>(). 

    How is this possible?  Is it something with my linq statement?  Something wrong with linq?  Am I in the wrong forum?


    • Edited by donc_bfe Sunday, July 13, 2014 12:36 AM
    Sunday, July 13, 2014 12:35 AM
  • I tried chaining the inner "where exists" queries using Any:

    var vfsl = (from f in vdb.vFormulaSelects
    where (
        (f.SUPPID.Equals(dv.SUPPID) || (dv.SUPPID.Equals(null) && f.SUPPID.Equals(null)))
        && f.TYPCD == dv.DONTYP
        && (vdb.vFormulaSelects.Any(ps1 => cmps.Contains(ps1.COMPCD) && ps1.ProductStage.Equals("F") && ps1.SETID.Equals(f.SETID)))
        && (vdb.vFormulaSelects.Any(ps2 => cmps.Contains(ps2.COMPCD) && ps2.ProductStage.Equals("I") && ps2.SETID.Equals(f.SETID))
            || !vdb.ComponentCodes.Any(ps3 => ps3.ProductStage == "I" && ps3.SETID == f.SETID))
        )
    select f);

    And the generated SQL was exactly the same as previously posted, which does return the correct results in SQL Management studio but NOT the framework .ToList<>() call. 

    Why are they different?


    • Edited by donc_bfe Sunday, July 13, 2014 7:44 PM
    Sunday, July 13, 2014 7:43 PM
  • Sort of interesting that using .Distinct() in the following variation still returned 7 rows in this case, which is correct, but every row from ToList are still exactly the same. 

    var vfsl = vdb.vFormulaSelects
    .Where (f => 
    	(f.SUPPID.Equals(dv.SUPPID) || (dv.SUPPID.Equals(null) && f.SUPPID.Equals(null)))
    	&& f.TYPCD == dv.DONTYP
    	&& (vdb.vFormulaSelects.Any(ps1 => cmps.Contains(ps1.COMPCD) && ps1.ProductStage.Equals("F") && ps1.SETID.Equals(f.SETID)))
    	&& (vdb.vFormulaSelects.Any(ps2 => cmps.Contains(ps2.COMPCD) && ps2.ProductStage.Equals("I") && ps2.SETID.Equals(f.SETID))
    		|| !vdb.ComponentCodes.Any(ps3 => ps3.ProductStage == "I" && ps3.SETID == f.SETID))
    	)
    .Select(f => f)
    .Distinct();
    
    string sql = ((System.Data.Entity.Infrastructure.DbQuery<vFormulaSelect>)vfsl).ToString();
    System.Diagnostics.Debug.WriteLine(sql);
    
    fsl = vfsl.ToList<vFormulaSelect>();

    Sunday, July 13, 2014 8:14 PM
  • Hello,

    This issue sounds very strange. Do you have the SQL Server Profiler? If you have, please try to track this executed TSQL when calling .ToList<T>() method.

    If the TSQL executed in the profiler is the same, please provide your demo project and tables information with us, you can upload them to:

    https://onedrive.live.com/

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, July 15, 2014 8:36 AM
    Moderator
  • Thank you for the response. 

    I do not have a server login and the only person available today is not familiar with Profiler.  If I can find someone tomorrow with knowledge about the setup I will post the file(s). 

    Tuesday, July 15, 2014 9:03 PM
  • Hello,

    Do you try to involve the mean who has the server login to check the exactly executed TSQL using SQL Profiler?

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 21, 2014 8:46 AM
    Moderator
  • Hi. 

    I did learn that it should be a Management Studio Tools menu item, but wasn't part of my installation.  I started through the Upgrade steps but didn't have the license key.  I know we have the license, but that guy was out of the office.  Meanwhile I had already changed my approach to the problem and eliminated the need for that query.

    I intend to come back to this in the next week or so when there's more time and provide that info. 

    Thanks for following up.

    Monday, July 21, 2014 1:56 PM