CODEWITHSUNDEEP
c#entity-frameworklinq-to-entitiessql-server-2012entity-framework-5
Alex
Alex

Reputation: 14503

Unexpected behaviour with LINQ to Entities, possible bug?

Background
I log searches in my application and want to present the ratio between the different types of searches in an statistics view.

Issue
I wanted to keep is as efficient and condensed as possible in LINQ but still keep it clear considering the limitations of what you can use in a LINQ query, so my attempt was this:

(Please disregard the fact that I'm not using a separate entity for the search types, this has it's reasons)

    var result = MyEntities.Instance.SearchStatistics
        .GroupBy(x => x.SearchType)
        .Select(y => new List<string> { { y.Key }, { SqlFunctions.StringConvert((decimal)y.Count()).Trim() } })
        .ToList();

    return Json(new
    {
        Text = result.First(x => x.ElementAt(0) == "Text").ElementAt(1),
        Organization = result.First(x => x.ElementAt(0) == "Organization").ElementAt(1),
        Subject = result.First(x => x.ElementAt(0) == "Subject").ElementAt(1),
    }, JsonRequestBehavior.AllowGet);

This behaved in an unexpected way though, the resulting List in the list of lists got it's values in every other row flipped.

I expected {{"Text", "123"}, {"Organization", "123"}, {"Subject", "123"},...}
but instead got {{"Text", "123"}, {"123", "Organization"}, {"Subject", "123"},...}

I did not understand why, and tried to partition the query 

    var preresult = MyEntities.Instance.SearchStatistics
        .GroupBy(x => x.SearchType).ToList();

    var result = preresult
        .Select(y => new List<string> { { y.Key }, { y.Count().ToString(CultureInfo.InvariantCulture) } }).ToList();

And now it worked as expected when only the grouping query was executed in the DB

Logging the resulting sql of the LINQ query to the DB, I got this result for the "faulty" one:

SELECT [Project3].[SearchType] AS [SearchType],
       [Project3].[C2]         AS [C1],
       [Project3].[C1]         AS [C2]
FROM   (SELECT CASE
                 WHEN ([UnionAll1].[C1] = 0) THEN [GroupBy1].[K1]
                 ELSE LTRIM(RTRIM(STR(CAST([GroupBy1].[A1] AS decimal(19, 0)))))
               END             AS [C1],
               [GroupBy1].[K1] AS [SearchType],
               1               AS [C2]
        FROM   (SELECT [Extent1].[SearchType] AS [K1],
                       COUNT(1)               AS [A1]
                FROM   [app].[SearchStatistic] AS [Extent1]
                GROUP  BY [Extent1].[SearchType]) AS [GroupBy1]
               CROSS JOIN (SELECT 0 AS [C1]
                           FROM   (SELECT 1 AS X) AS [SingleRowTable1]
                           UNION ALL


                           SELECT 1 AS [C1]
                           FROM   (SELECT 1 AS X) AS [SingleRowTable2]) AS [UnionAll1]) AS [Project3]
ORDER  BY [Project3].[SearchType] ASC,
          [Project3].[C2] ASC

Unfortunately I have limited experience in SQL and my head hurts just by looking at that, but something is obviously getting mixed up.

Can someone pinpoint where the problem in the sql query lies?

Is it reasonable that I would expect this as I want to? Could it be considered a bug, or is it just my expectations that have no support in the contract of how LINQ should work?

Update

Hold on, why did I not look at the output from the SQL query directly? The mind works in mysterious ways...

The sql query is returning this : 

[SearchType, C1, C2]
{Text, 1, Text}  
{Text, 1, 123}  
{Organization, 1, 123}  
{Organization, 1, Organization}  
{Subject, 1, Subject}  
{Subject, 1, 123}

But the result after .ToList() is still only containing three items. This is only making me more confused, surely must be some implicit internal behavior of LINQ that have unintended consequences?

Upvotes: 1

Views: 219

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109109

It's hard to believe that the order of name and value is flipped in the JSON object, but apparently it happens.

It may have to do with the fact that the order of items in a List<T> is never guaranteed to be equal to the insertion order. You can prevent this issue by not using a list at all:

var result = MyEntities.Instance.SearchStatistics
    .GroupBy(x => x.SearchType)
    .Select(y => new { y.Key, Count = y.Count() })
    .ToList();

return Json(new
{
    Text = result.First(x => x.Key == "Text").Count.ToString(),
    Organization = result.First(x => Key) == "Organization").Count.ToString(),
    Subject = result.First(x => x.Key == "Subject").Count.ToString(),
}, JsonRequestBehavior.AllowGet);

Upvotes: 1

Related Questions