How can I use a linq query in a select statement to determine a boolean with entity framework

Question

I am querying a set of Entity Framework objects and projecting them through a WCF RIA service into lightswitch.

In my query I perform a join between a couple of tables, one of which are details for a summary table. I want to determine which detail item is the 'first' item in the list and compare that to the current item in my projection during enumeration. The reason being in the projection I want to change which of my available properties sets this specific projection's property.

This is the query so reduced

var result = (from dataItem in (from x in Context.xs
              join y in Context.ys
              on x.property = y.property
              select new {x, y})
              .Select( (model, index) =>
                          new ObjectType 
                              { 
                                  Id = index,
                                  OneOfTwo = model.x.property,
                                  (
                                       from y in Context.ys 
                                       where y.SomeProperty == model.Property
                                       select y)
                                       .OrderBy(list=>list.Id)
                                       .FirstOrDefault()
                                       .ComparedProperty ==model.ComparedProperty
                                   ) 
                                   ? model.AnotherProperty
                                   : model.YetAnotherProerty
                              }
               );

How can I evaluate that inner query without running into DataReader errors because I'm trying to use the Context while it is being read?

Update:
I did some research and realize that this is not a problem with linq to sql but it is a problem with linq to entities. Essentially that operation is the equivalent of using a parameterized query which isn't supported. What are the workarounds in this situation?

I'm having trouble wrapping my head around your pseudo-code. Is `model.x.property` really different than `model.Property`? What would be helpful is if you could give us some pseudo-data that goes with this code and then what output you're looking to achieve. It's helpful to see what data you're starting out with and how you're trying to shape it. — Brad Rem, Jun 22 '12 at 19:41
Alright here's what I start out with Order {Id, UserId, Date, PromoCodeId} OrderItem {Id, OrderId, ProductId} PromotionPrice {Id, PromoCodeId, ProductId, Price, AdditionalItemPrice} I'm trying to get a view that encapsulates the following fields {OrderId, OrderItemId, UserId, PromoCodeId, PromotionPrice} The problem is with regards, when grouping by OrderId, I need to determine if a specific product is the first or not of its kind and assign the appropriate price in the promotionprice. Linq to entities will not allow me to do this without looping over my entire view size twice. — The Prophet, Jun 25 '12 at 12:42

score 0 · Answer 1 · answered Jun 24 '12 at 06:53

0

LightSwitch won't let you have an entity whose properties are dynamic. It has to be given a concrete entity, so I don't think you're going to be able to do what you want, unless of course the dynamic properties are of the same type (which you haven't specified).

answered Jun 24 '12 at 06:53

Yann Duran

3,842
1
24
24

Well to be fair, I'm doing performing that linq statement in a WCF RIA service before it is sent to lightswitch. However, I am still running into linq to entities trouble since my WCF RIA services uses the entities that I have in lightswitch which were generated from sql server database that was attached to lightswitch – The Prophet Jun 25 '12 at 12:44
The linq statement generates instances of a POCO entity who fields are constant, however, I'm trying to make dynamic which of my table columns populates that field. In order words, I'm wanting to set the same field differently based on whether or not a record is first or not. – The Prophet Jun 25 '12 at 12:47
OK, that makes sense then. Now you need to tell us more about how it's not working. What's the exact error message that you're getting? – Yann Duran Jun 25 '12 at 23:03
I'm getting the DataReader is still open message and that I need to close it before performing the inner query. "There is already an open DataReader associated with this Command which must be closed first.." Its a limitation of linq to entities. My workaround currently is O(2N) of what I had intended since I have to loop twice. – The Prophet Jun 26 '12 at 15:43
I'd look at "Composing" your query, using several separate LINQ statements, which will not only make it easier to read & understand at a glance, it may also solve your "DataReader" problem. – Yann Duran Jun 26 '12 at 23:28

score 0 · Accepted Answer · answered Sep 25 '12 at 18:15

0

The solution was to project the data to a List collection that I needed to data transfer objects and utilize linq to objects. I then exposed my collections through a WCFRIA service back to lightswitch.

answered Sep 25 '12 at 18:15

The Prophet

338
5
11

How can I use a linq query in a select statement to determine a boolean with entity framework

2 Answers2