The LINQ "let" statment.

Posts   
 
    
Brandt
User
Posts: 142
Joined: 04-Apr-2007
# Posted on: 11-Nov-2008 04:40:58   

I am muddling through the Commerce.MVC project (http://blog.wekeroad.com/mvc-storefront/mvc-storefront-preview-1-available/?disqus_reply=3672793#comment-3672793) which has a really nice LINQ pattern for repositories and services however LLBLGEN's LINQ provider doesn't like the "let" construct that is used. The "let" construct isn't needed however I would like to know why LINQ to Entities doesn't throw up but LINQ to LLBLGen does. Here is a code example from that project.



        public IQueryable<Order> GetOrders() {

            var orders = from o in _db.Orders

//*******
                         let items = GetOrderItems(o.OrderID)
                         let transactions = GetTransactions(o.OrderID)
//*******

                       select new Order
                                    {
                                        Status = (OrderStatus)o.OrderStatusID,
                                        DateCreated = o.CreatedOn,
                                        ID = o.OrderID,
                                        OrderNumber = o.OrderNumber,

//*******
                                       Items = new LazyList<OrderItem>(items),
                                        Transactions = new LazyList<Transaction>(transactions),
//*******

                                        ShippingAddress = GetAddresses().Where(x => x.ID == o.ShippingAddressID).SingleOrDefault(),
                                        BillingAddress = GetAddresses().Where(x => x.ID == o.BillingAddressID).SingleOrDefault(),
                                        ShippingMethod =GetOrderShippingMethod(o.ShippingMethod, o),
                                        UserName = o.UserName,
                                        UserLanguageCode=o.UserLanguageCode,
                                        DateShipped=o.DateShipped,
                                        EstimatedDelivery = o.EstimatedDelivery,
                                        TrackingNumber=o.TrackingNumber,
                                        TaxAmount=o.TaxAmount,
                                        DiscountReason=o.DiscountReason,
                                        DiscountAmount=o.DiscountAmount
                                        
                                    };
            return orders;

        }


        IQueryable<OrderItem> GetOrderItems(Guid orderID)
        {

            return from oi in GetOrderItems()
                   where oi.OrderID == orderID
                   select oi;
        }


        public IQueryable<OrderItem> GetOrderItems() {
           SqlCatalogRepository catalog = new SqlCatalogRepository(_db);

            return from oi in _db.OrderItems
                   select new OrderItem
                              {
                                  OrderID = oi.OrderID,
                                  Quantity = oi.Quantity,
                                  DateAdded=oi.DateAdded,
                                  LineItemPrice=oi.LineItemPrice, 
                                  Product = (from p in catalog.GetProducts()
                                             where p.ID == oi.ProductID
                                             select p
                                            ).SingleOrDefault(),
                                            

                              };
        }


I left out the implementation for the LazyList but all it does is take a IQueryable<> and convert it to an IList when it is accessed. Now the "let" statements aren't needed because you could just call "Items = new LazyList<OrderItem>(GetOrderItems(o.OrderID))", and that works with the LINQ to LLBLGen provider however when the let statments are used the query to SQL is completely messed up. Just commenting the let statement out everything works fine again. Whats going on here?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 11-Nov-2008 10:10:26   

Use 'let' with care. Every 'let' statement wraps the inner query in a complete SELECT statement using a derived table. Using 'let' extensively therefore will create slower queries. If you want / need to use imperative programming with the linq data, consider C# code outside the query.

The above is qouted from the following manual section: Remarks on several extension methods and constructs

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 11-Nov-2008 10:50:28   

Also, you do use the latest build of the runtime? Not that there have been recent let related code changes, but just in case.

(edit). The let keyword for database queries is really a pain. The problem is that the let statement as quoted by Walaa, is actually a separate query, so the results have to be dragged along with every operation. As this in general leads to very inefficient queries, it's in general adviced not to use let unless you absolutely know what you're doing.

That said, in theory it should work, the provider does support let, though perhaps not in all cases as you're query suggests. That the entity framework does work with this query is perhaps no surprise: they can borrow from the linq to sql sourcecode and the knowledge behind it and also ask the people who wrote the compiler and linq syntax about what to do. We can't, unfortunately, so what we can do is look at what might be a good approach, try it out, and either decide it works or try again. simple_smile Our let tests work, but these are using let to store single values for example, like a scalar query result, as it's pretty inefficient to use let to store resultsets (and I also think that there are let using queries which will likely never be possible in SQL, as multiple usage of let requires that multiple sets of data are kept around, but there's no other way than to join them together but you then have to take into account duplicates which might occur... not something which is always possible to solve, especially if further SQL operations are required on the data stored in the let parameter. )

Frans Bouma | Lead developer LLBLGen Pro
Brandt
User
Posts: 142
Joined: 04-Apr-2007
# Posted on: 11-Nov-2008 15:51:05   

Thanks, Walaa and Otis. I did read the docs before I posted (usually i am bad about that) and saw the notes about the let statement. I just wanted more clarification about "let" and how it is should be used. I am going to read the msdn docs and see what they have.

I will stay away from let for the time being.

Thanks, Brandt