CODEWITHSUNDEEP
c#winformslinqentity
Martin
Martin

Reputation: 65

Create a dynamic LINQ Query to cater for different conditions

I have a LINQ query to populate an object (which is then the datasource for a grid) with a few joins.

I want the query to be dynamic so it retrieves rows based on parameters passed, but so far it doesn't work as soon as the StatusID is passed in - it brings back all instances (as if a cartesion product is happening)

_viewfetch.POMastStatusID will either be -1 or a value of 1 or above.

QUERY:

var queryforobject = from p in db.POMasts.AsNoTracking()
                     join pr in db.Profiles.AsNoTracking() on p.ProfileID equals pr.ID
                     join c in db.CurrencyTypes.AsNoTracking() on p.CurrencyTypeID equals c.ID
                     join w in db.WHMasts.AsNoTracking() on p.WarehouseID equals w.ID
                     join t in db.TermCodeTypes.AsNoTracking() on p.TermCodeTypeID equals t.ID
                     join s in db.POMastStatusTypes.AsNoTracking() on p.StatusID equals s.ID

                     //Ensure that these are dynamic
                     where _viewfetch.VendMastID == -1 || p.VendorID == _viewfetch.VendMastID && 
                     _viewfetch.POMastStatusID == -1 || p.StatusID == _viewfetch.POMastStatusID

                     orderby p.ID

                     //Put the query results into the bespoke object
                     select new POMastObject { ID = p.ID, 
                                               OrderNo = p.OrderNo, 
                                               RaisedDate = p.RaisedDate, 
                                               RaisedBy = pr.Name, 
                                               Currency = c.Description, 
                                               Warehouse = w.Description, 
                                               Terms = t.Description, 
                                               LastEditedBy = p.LastEditedBy, 
                                               LastEditedDate = p.LastEditedDate, 
                                               Status = s.Name };
if (queryforobject.Count() > 0)
    _dataobject = queryforobject.ToList();

Does anyone have any suggestions?

Upvotes: 0

Views: 46

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726649

Your query is fine, it's just missing parentheses around the two parts of the && operator:

 where (_viewfetch.VendMastID == -1 || p.VendorID == _viewfetch.VendMastID) && 
       (_viewfetch.POMastStatusID == -1 || p.StatusID == _viewfetch.POMastStatusID)

Since && has higher precedence than ||, your query effectively evaluates with the two conditions in the middle AND-ed together, like this:

_viewfetch.VendMastID == -1 || (p.VendorID == _viewfetch.VendMastID && _viewfetch.POMastStatusID == -1) || p.StatusID == _viewfetch.POMastStatusID

This is not the logic that you are looking for, because when VendMastID is -1 you get all rows.

Upvotes: 1

Related Questions