none
Error in EntityFramework and LinQ-Select when target is List<string> RRS feed

  • Question

  • Hello,

    i realized an error in LinqToSQL, when the target type has an List<string>:

    X.Select(item => new List<string> { item.FirstColumn, item.SecondColumn});
     

    This Linq-Statement leads to an SQL, which builds 2 records for each source record, which are the same except one "Switch-column (C1)" which alternates between FirstColumn and SecondColumn. and one additional column (C2) which contains always 1. (It should contain 0 and 1, which is in UnionAll1.C1)

    Select1:

     SELECT 
            CASE WHEN ([UnionAll1].[C1] = 0) THEN [Extent1].[FirstColumn] ELSE [Extent1].[SecondColumn] END AS [C1], 
            [Extent1].[PrimaryKey] AS [PrimaryKey], 
            1 AS [C2]
            FROM  [dbo].[X] AS [Extent1]
            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]

    And then the SQL embraces this by another Select which added an "OrderBy". But this OrderBy belongs to the wrong (and needless?) column which contains the 1.

    Select2:

    SELECT 
        [Project3].[PrimaryKey] AS [PrimaryKey], 
        [Project3].[C2] AS [C1], 
        [Project3].[C1] AS [C2]
        FROM ( ... )  AS [Project3]
        ORDER BY [Project3].[PrimaryKey] ASC, [Project3].[C2] ASC

    Now the OrderBy leads to a column which contain 1 and orders nothing.

    In our Project the SQL is a large and complex chain of linq-commands. This error leads to an mixed List<string>.

    In Simple SQLs the error has no effect, because the sorting is automatically right.

    I have no idea, if i should post this error here, so please tell me, if there is a better place.

    Greeting

    Kirsten


    Monday, December 8, 2014 8:59 AM

Answers

  • Hello Kirsten,

    >>But is there anybody to fix the error? Because someone wrote the code which isn't useful. Is there at least someone who is interrested in knowing the error to get the chance to fix it?

    I am afraid we have no chance to modify the sql statement since it is produced by provider, of course, you could write a custom provider which meets your requirement, however, I do not think it is easy.

    Since you cannot write the store produce, if it is possible for your write sql statement directly, if you could, you can write SQL queries for entities using raw SQL directly against the database:

    http://msdn.microsoft.com/en-us/data/jj592907.aspx

    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.

    Thursday, December 11, 2014 7:45 AM
    Moderator

All replies

  • Hello,

    >>This Linq-Statement leads to an SQL…

    I am confused why the LINQ query would be so complex, because according to your provided linq query I create a test demo:

    var result = db.Orders.Select(item => new List<string> { item.OrderID.ToString(), item.OrderName.ToString() });

    The generated sql statement is very simple:

    SELECT CONVERT(NVarChar,[t0].[OrderID]) AS [value], CONVERT(NVarChar,[t0].[OrderName]) AS [value2]
    
    FROM [dbo].[Orders] AS [t0]
    

    I use VS2013,.NET 4.5 and LINQ2SQL project with SQL Servr2012 express.

    And for this linq query, I am not sure if you have tried to iterate the result, I should tell that this query would fail because with error message below:

    Cannot create a query result of type 'System.Collections.Generic.List`1[System.String]'.

    Is it the same with the one you got? If it is, unfortunately, since the query would a IQuerable<T> type, it would translate the LINQ query to sql statement, however, the List<T> recognized type is not by the provider.

    One way is to LINQ2Object as below using the ToList() to load all data to local firstly, of course, it would affect the performance:

    var result = (from order in db.Orders.ToList()
    
                                      join od in db.OrderDetails on order.OrderID equals od.OrderID
    
                                      select new List<string> { order.OrderName.ToString(), od.OrderDetailName });
    

    If i do not understand this issue correctly, please feel free let me know and please also post the error message here.

    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, December 9, 2014 2:24 AM
    Moderator
  • Hello Fred,

    thank you for your reply.

    I built a testproject to have a Repro, and it's very simple. I start with an EntityFramework (6.1.1) Context from Northwind database with only Employee-table selected.

    The method is:

    using System; using System.Collections.Generic; using System.Linq; public class EmployeeBO { public List<List<string>> getList() { using (var context = new NorthwindEntities()) { var names = context.Employees
    .Select(e => new List<string>
    { e.LastName, e.FirstName}); return names.ToList(); } } }

    Before i return the list the Debugger says in :

    ((System.Data.Entity.Infrastructur.DbQuery<System.Collections.Generic.List<string>>)names).InternalQuery

    SELECT 
        [Project3].[EmployeeID] AS [EmployeeID], 
        [Project3].[C2] AS [C1], 
        [Project3].[C1] AS [C2]
        FROM ( SELECT 
            CASE WHEN ([UnionAll1].[C1] = 0) THEN [Extent1].[LastName] ELSE [Extent1].[FirstName] END AS [C1], 
            [Extent1].[EmployeeID] AS [EmployeeID], 
            1 AS [C2]
            FROM  [dbo].[Employees] AS [Extent1]
            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].[EmployeeID] ASC, [Project3].[C2] ASC

    I got no "error" but the SQL is wrong as i explained in the post. I have no Linq2SQL-Project(?) but i'm using VS Pro 2013 V. 12.0.31101.00 Upd 4 mit MS .NET Framework V. 4.5.51641. NorthwindDatabase is connected with SQLexpress (SQL Server 2012 (11.0.2100)).

    In my other project where the SQL leads to a mixed list of strings i could avoid the mixing by enumerating after each filter with .ToList(). But that's really not what we want ;-)

    Regards

    Kirsten

     


    Tuesday, December 9, 2014 8:55 AM
  • Hello Kirsten,

    I assume that you use LINQ2SQL because as I see that you posted this issue to this forum which is similar with Entity Framework, I help move it to an appropriate forum since you are Entity Framework.

    With your clarification, I also make a test with Entity Framework and it generates the sql statement as you mentioned. Since the script is generated by provided, I think we cannot modify it to what we want with the LINQ query.

    I am not sure why you want use the List<T> inside the LINQ query, while I think the complex query might affect the performance, so I suggest that you could consider using the stored procedure to replace the LINQ query:

    http://www.entityframeworktutorial.net/stored-procedure-in-entity-framework.aspx

    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.

    Wednesday, December 10, 2014 9:43 AM
    Moderator
  • Linq-2-SQL has its own MSDN forum and is where you should post.

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=linqtosql

    Wednesday, December 10, 2014 9:54 AM
  • Hello Fred,

    ok, than it ' s EntityFramework with LinqToEntities.

    But is there anybody to fix the error? Because someone wrote the code which isn't useful. Is there at least someone who is interrested in knowing the error to get the chance to fix it?

    Ofcourse, there are ways to get around this issue, but in our scenario it seemed to be the best way, for in our project we want to build lists with different Count of strings out of a picklist table.

    The stored procedure is not a way for us, because we can't make changes in the database,

    Now i worked around with  many different types, but it is a bunch of work to work araound.

    Regards

    Kirsten

    Wednesday, December 10, 2014 8:22 PM
  • No darnold, please follow the thread. It IS an error of EntityFramework and LinQ and the SQL which comes out there.

    And the thread has been moved from the Linq-2-SQL Forum. Only the title is wrong, because i didn't know exactly what team is supposed to be informed.

    Regards

    (i will try to change the title)

    Wednesday, December 10, 2014 8:29 PM
  • Hello Kirsten,

    >>But is there anybody to fix the error? Because someone wrote the code which isn't useful. Is there at least someone who is interrested in knowing the error to get the chance to fix it?

    I am afraid we have no chance to modify the sql statement since it is produced by provider, of course, you could write a custom provider which meets your requirement, however, I do not think it is easy.

    Since you cannot write the store produce, if it is possible for your write sql statement directly, if you could, you can write SQL queries for entities using raw SQL directly against the database:

    http://msdn.microsoft.com/en-us/data/jj592907.aspx

    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.

    Thursday, December 11, 2014 7:45 AM
    Moderator
  • Hello, i realized an error in LinqToSQL,

    That's what you posted. It was an immediate disinterest for me and a thread step-over the entier thread.

    Thursday, December 11, 2014 4:59 PM