locked
Converting Linq-to-Entities query into SQL RRS feed

  • Question

  • hi


    var employees = from c in context.Employees
                              let c1 = new { c.FirstName }
                              select c1;
    


    The above Linq-to-Entities query is converted by EF into: 


    SELECT 1                                 AS [C1],
                 [Extent1].[FirstName] AS [FirstName]
    FROM    [dbo].[ Employee] AS [Extent1]
    


    I don't understand why Sql query has to return column C1 ( SELECT 1 AS [C1] )?

    Thank you






    • Edited by KlemS100 Tuesday, October 18, 2011 5:59 PM
    Tuesday, October 18, 2011 5:56 PM

Answers

All replies

  • Hi,

    Thank you for reporting this observation. The column C1 is produced by the Entity Framework to track possible nullability. While in this particular case it is not possible to result in a row that is null, in more complex scenarios it is.

    However, in general, if the Entity Framework can determine that it can use another column for the same purpose it would not produce the additional column.

    Thanks,
    Kati Iceva,

    Entity Framework developer   

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, October 18, 2011 6:48 PM
  • Hi KlemS100;

    I believe that this is due to the fact that you are creating an anonymous type and assigning it to the Linq range variable C1. If you were to remove the new keyword and { and } from the query you would get the following results.

    var employees = from c in context.Employees
    		let c1 = c.FirstName
    		select c1;
    
    SELECT [Extent1].[FirstName] AS [FirstName]
    FROM [dbo].[Employee] AS [Extent1]
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Tuesday, October 18, 2011 7:40 PM
  • Hi,

     The column C1 is produced by the Entity Framework to track possible nullability

    hi, I don't quite understand your argument. Namely, why exactly must EF track for possible nullability and why must it track for nulability only if we create an anonymous type and assign it to C1 range variable? Thus why doesn't EF also track nulabillity with the following Linq-to-Entities query ( thus why in this case resulting Sql query doesn't return C1 column ): 

    var employees = from c in context.Employees
    	                   select c1;
    
    


    Wednesday, October 19, 2011 6:58 PM
  • Hi KlemS100,

    I think you can refer this link: http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataservices/thread/fc3996ef-5ba1-4401-83b7-ad95fd9717bf

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by KlemS100 Tuesday, October 25, 2011 6:05 PM
    Tuesday, October 25, 2011 8:04 AM
  • thank you
    • Proposed as answer by VladSand Friday, February 17, 2012 10:54 AM
    • Unproposed as answer by VladSand Friday, February 17, 2012 10:54 AM
    Tuesday, October 25, 2011 6:00 PM
  • Hi, is there any way I can "force" the EF to include a nullability column in the result? My question is really if I can predict or choose the exact order of the columns the sql generation through ToTraceString() returns.

    I use ToTraceString() for an IQueryable to get the resulted SQL command and then insert the results directly in a database table.

    So, I kind of need the generated SQL to be consistent with my table structure...

    string insertQuery = 
                    string.Format("INSERT INTO {0} {1}", 
                        sqlTableName, 
                        ((System.Data.Objects.ObjectQuery<TRow>)results).ToTraceString());

                Context.ExecuteStoreCommand(string.Format("TRUNCATE TABLE {0}", sqlTableName));
                Context.ExecuteStoreCommand(insertQuery);

    results = IQueryable<Row> where Row is a type with the same properties as the table columns

    I choose to do a direct insert into a table because i see no point in getting a ToList() enumerable on the webserver, just to send it back to SQL through some kind of bulk insert (which EF doesn;t support for the moment...) My query returns a considerable amount of rows and I do not want to use Stored Procedures.

    Hope I make sense...thanks



    • Edited by VladSand Friday, February 17, 2012 11:02 AM
    Friday, February 17, 2012 10:59 AM