Help with Entity Query

Question

I can't seem to figure out how to write this query properly. I've tried various combinations but nothing's worked yet.

Below is the relevant portion of my database model: alt text

I need to select the products that match a given Category and Group, and that match a given Year, Make, Model, submodel. This I've done below:

 ItemList = From P In gDataContext.Products.Include("Groups").Include("Groups.Category1").Include("LookupYearMakeModels") From G In P.Groups Where G.Category = Cat And G.Grp = Group  From Y In P.LookupYearMakeModels Where Y.Year = YMM.Year And Y.Make = YMM.Make And Y.Model = YMM.Model And Y.Submodel = YMM.Submodel Select P

I now also have to select products that match the Category, and Group but are Universal (Product.Univeral = True).

I'm currently writing two queries, the one above and the one below. I'm merging the results of the two by simply using ItemList.AddRange(ItemList2)

ItemList2 = From P In gDataContext.Products.Include("Groups").Include("Groups.Category1") where P.Universal From G In P.Groups Where G.Category = Cat And G.Grp = Group  Select P

But I want to combine both queries into one and avoid merging the results. How can I do it?

Thanks for your help!

I would stay away from using reserve words like Group as a name for one of my objects. — JBrooks
– JBrooks, Commented Oct 20, 2013 at 18:13

Jeff Ogata · Accepted Answer · 2010-11-14 19:09:33Z

2

+50

I tried to set up a similar model, and I believe this works. Here I am selecting products that have a group matching the given category and group and that have a matching year/make/model/submodel or are universal.

ItemList = From P In gDataContext.Products.Include("Groups").Include("Groups.Category1").Include("LookupYearMakeModels") 
           Where P.Groups.Any(Function(G) G.Category = Cat And G.Grp = Group) _
                And ( _
                        P.LookupYearMakeModels.Any(Function(Y) Y.Year = YMM.Year And Y.Make = YMM.Make And Y.Model = YMM.Model And Y.Submodel = YMM.Submodel) _
                        Or _
                        P.Universal = True _
                    )
           Select P

HTH

answered Nov 14, 2010 at 19:09

Jeff Ogata

58k20 gold badges117 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jeff Ogata Over a year ago

@mga911, great, glad it worked. Does this mean I get the bounty :) ?

mga911 Over a year ago

Yes and I marked your answer as the accepted answer. If it didn't credit you the points yet then you'll probably get them when the open bounty period ends tomorrow. Thanks again

mga911 Over a year ago

I had to click on the bounty to award it. I think you have it now.

Morteza Manavi · Accepted Answer · 2010-11-06 15:14:38Z

0

You can use IQueryable.Union Method:

ItemList = (From P In gDataContext.Products                                 
                                  .Include("Groups.Category1")
                                  .Include("LookupYearMakeModels") 
            From G In P.Groups 
                Where G.Category = Cat And G.Grp = Group  
            From Y In P.LookupYearMakeModels 
                Where Y.Year = YMM.Year 
                     And Y.Make = YMM.Make And Y.Model = YMM.Model 
                     And Y.Submodel = YMM.Submodel 
            Select P)
            .Union(
            From P In gDataContext.Products                     
                                  .Include("Groups.Category1")
            Where P.Universal
            From G In P.Groups Where G.Category = Cat And G.Grp = Group
            Select P)

answered Nov 6, 2010 at 15:14

Morteza Manavi

33.2k6 gold badges103 silver badges84 bronze badges

1 Comment

mga911 Over a year ago

I appreciate the answer but I'm looking for a way to make the two queries into one query not just merged the results.

Collectives™ on Stack Overflow

Help with Entity Query

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related