LinQ and WCF service Operation

Question

I am having a problem getting some LinQ to work inside a WCF service operation:

[WebGet]
public IQueryable<StockItem> AllStockableItems(int LocationAddressId)
    {
    StockEntities svc = this.CurrentDataSource;

    //get all the stock at a location
    var StockAtLocation = from s in svc.Stock
                          where s.Location == Location
                          select s;

    //weave it into the list of all stockable items
    var StockableItems = from si in svc.StockableItems
                         join s in StockAtLocation on si.ItemId equals s.ItemId into tmp
                         select si <and somehow expand> si.Stock;

    return StockableItems;
    }

Problem is, I don't know how to expand the stock in the returned data?

A url like the following:

....my.svc/AllStockableItems?LocationAddressId=3&$expand=Stock

Will expand the stock for all locations, rather than just the location needed. Is this possible or is my best bet to make 2 seperate requests from the Silverlight client and do the joining client side?

Any help greatly appreciated.

Yes, example data, sorry for not putting it in first time around: example stock data:

ItemId    Location   Quantity
   1         1           4
   1         2           3
   1         3           2
   2         2           6
   3         3           0
   7         1           3
   7         2           0

example stockableItems data

 ItemId   <other columns>..
   1
   2
   3
   4
   5
   6
   7
   8

Say the locationAddressId paramter =2, I'm trying get the service operation to return (not literally, but in the Atom/Pub equivalent):

StockableItem  { ItemId :1 
                 Stock { 
                     entry { 
                           Stock {LocationId : 2, Qty :4} 
                           } 
                       } 
               }
StockableItem  { ItemId :2 }
StockableItem  { ItemId :3 }
StockableItem  { ItemId :4 }
StockableItem  { ItemId :5 }
StockableItem  { ItemId :6 }
StockableItem  { ItemId :7 
             Stock { 
                     entry { 
                           Stock {LocationId : 2, Qty :0} 
                           } 
                   } 
               }
StockableItem  { ItemId :8 }

Thank you.

[Update 2]

Ok, I've tried a couple fo things; first off I gave this a go:

var StockableItems = from si in svc.AllStockableItems
                     join s in svc.Stock on si.ItemId equals s.ItemId
                     where s.Location == Location
                     select  new StockableItem
                           {
                           ItemId = s.ItemId,
                           Stock = new EntityCollection<Stock>
                                    {
                                    new Stock()
                                        {
                                        Location = s.Location,
                                        Quantity= s.Quantity
                                        }
                                    }
                           };

Which gave me:

The entity or complex type '...' cannot be constructed in a LINQ to Entities query

Which led me to here:

The entity cannot be constructed in a LINQ to Entities query

Which led me to re-write the query to this:

var StockableItems = svc.AllStockableItems
                    .Join(svc.Stock, si => si.ItemId, s => s.ItemId, (si, s) => si)
                    .ToList()
                    .Select(si => new StockableItem
                       {
                       ItemId = si.ItemId,
                       Stock = new EntityCollection<Stock>
                           {
                           new Stock()
                                {
                                Location = si.Stock.First().Location,
                                Quantity= si.Stock.First().Quantity
                                }
                           }
                       })
                       .AsQueryable();

Which returns all the StockableItems but, somewhat frustratingly, doesn't include any Stock. Have I simply made a boo boo in this last query? I suspect my inner projection of the Stock entity is incorrect?

Thanks Again

Answer 1

I think you're looking for something like this:

var StockableItems = from si in svc.StockableItems
    join s in StockAtLocation on si.ItemId equals s.ItemId
    select new
    {
        StockableItem = si,
        Stock = s
    };

You can choose how you project your output in the select clause. You can select the entire object, like above, or you can also select fields in your objects. For example:

    select new
    {
        ItemId = si.ItemId,
        Stock = s,
        Qty = s.Quantity
    };

Also, you may want to consider combining your two queries into a single query:

var StockableItems = from si in svc.StockableItems
    join s in svc.Stock on si.ItemId equals s.ItemId
    where s.Location == Location
    select new
    {
        StockableItem = si,
        Stock = s
    };

And now one more example that shows you something really close to your example output:

var StockableItems = from si in svc.StockableItems
    join s in svc.Stock on si.ItemId equals s.ItemId
    where s.Location == Location
    select new
    {
        StockableItem = new
        {
            ItemId = s.ItemId,
            Stock = new
            {
                LocationId = s.Location,
                Qty = s.Quantity
            }
        }
    };

Update

I've made some adjustments to your modified query which uses the Join to create the data that is passed onto the rest of the query. I also put your Where clause back in there (if you want to use it there) to filter on Location. I also took the ToList() out of there because I'm not sure if it's necessary.

var StockableItems = svc.AllStockableItems 
                .Join(svc.Stock, si => si.ItemId, s => s.ItemId, 
                      (si, s) => new 
                      { 
                          ItemId = si.ItemId, 
                          Location = s.Location, 
                          Quantity = s.Quantity
                      }) 
                .Where(x => x.Location == Location)
                //.ToList() 
                .Select(x => new StockableItem 
                   { 
                   ItemId = x.ItemId, 
                   Stock = new EntityCollection<Stock> 
                       { 
                       new Stock() 
                            { 
                            Location = x.Location, 
                            Quantity= x.Quantity 
                            } 
                       } 
                   }) 
                   .AsQueryable();