none
IQueryable when returning other class than entity

    Question

  • if you have a method which returns an IQueryable then you can add where statement etc and it will get added to the final sql query when the final query is executed (for example when I do ToList()). 

    My question is how this works if your linq query returns another class than entity, i.e you do something like select new MyObject { .. }.

    Does it work ? Will selections get propogated to the database level (i.e. the resulting sql query) and if so how will it handle when a selection is on a columns which might only be in the object I return? Will it filter in-memory or just cause an exception at runtime ? 


    Thursday, July 03, 2014 10:11 AM

Answers

  • Hello,

    The * is accepted by the provider which means it can be translated to correspondent TSQL, the above query below

    var result = (from order in db.OrderDetails
                                      select new OrderDTO { OrderID = order.OrderID * order.OrderDetailID });
    
                        var orderid = result.Where(d => d.OrderID > 1).ToList();

    which is similar with yours will be translated to:

    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 07, 2014 1:23 AM
    Moderator

All replies

  • Hello,

    >>My question is how this works if your linq query returns another class than entity, i.e you do something like select new MyObject { .. }.

    Yes, it could work. This is called projection, Projection in Linq to Entities (Linq with Entity Framework models) is very similar to the way it is done in T-SQL, a column or columns are queried and set to return naming different than the original column name.

    >>Will selections get propogated to the database level (i.e. the resulting sql query) and if so how will it handle when a selection is on a columns which might only be in the object I return?

    It would get propogated to the database level and if you only query one column, it will generate the TSQL for querying that specific column only. For example, if you write below LINQ query:

    var result = (from order in db.OrderDetails
    
                                      select new OrderDTO { OrderID = order.OrderID }).ToList();
    

    It will generate TSQL for query the OrderID column in OrderDetails table only:

    >> Will it filter in-memory or just cause an exception at runtime?

    No, if using IQueryable, it will filter in database, for IEnumable and ToList, it will filter in memory which will load all data to memory firstly.

    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.

    Friday, July 04, 2014 1:48 AM
    Moderator
  • Thank you.

    Yes I guess it would work just fine for straight projection of columns. But let say we have something like this (to expand on your example):

    If that is returned as a IQueryable (question is though if it should be returned as a Iqueryable<OrderDetail> or IQueryable<OrderDTO> would it be possible to add a where on RowAmount or CustomerName columns.

    var result = (from order in db.OrderDetails select new OrderDTO { OrderID = order.OrderID, CustomerName=order.OrderHeader.Customer.Name, RowAmount = (order.Quantity * order.Price) });

    Let say we return this in a method call MyQuery() as Iqueryble and do somehting like

    var result = MyQuery().Where(d=> d.RowAmount > 1000).ToList();

    • Edited by magnusb999 Friday, July 04, 2014 9:40 AM
    Friday, July 04, 2014 9:38 AM
  • Hello,

    The * is accepted by the provider which means it can be translated to correspondent TSQL, the above query below

    var result = (from order in db.OrderDetails
                                      select new OrderDTO { OrderID = order.OrderID * order.OrderDetailID });
    
                        var orderid = result.Where(d => d.OrderID > 1).ToList();

    which is similar with yours will be translated to:

    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 07, 2014 1:23 AM
    Moderator