Databases
StackOverflow2013
Postsdbo
POHow to do a search with EF CodeFirst
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to do a search with EF CodeFirst
    text
    copied!<p>Currently, to do a search using EF CodeFirst and a repository pattern, based on user input to multiple text boxes on an mvc search view/page, I do something like the following:</p> <pre><code> public PagedList&lt;Entity1&gt; PlayerUserSearch(Entity1SearchParameters searchParameters, int? pageSize, int? startEntity, Func&lt;Entity1, object&gt; sortOrder, bool sortDesc) { IQueryable&lt;Entity1&gt; query = from entities in this.DataContext.Entity1s.Include("Entity2List") where entities.Entity2List.Any() select entities; if (searchParameters.Entity2PrimaryKeyId.HasValue) query = query.Where(e =&gt; e.Id == searchParameters.Entity2PrimaryKeyId.Value); if (searchParameters.HasStats.HasValue) { if (searchParameters.HasStats.Value) query = query.Where(u =&gt; u.Entity2List.Any(e =&gt; e.Stat != null)); else query = query.Where(u =&gt; u.Entity2List.Any(e =&gt; e.Stat == null)); } if (searchParameters.Entity2OtherField.HasValue) query = query.Where(u =&gt; u.Entity2List.Any(e =&gt; e.Event.Entity2OtherField == searchParameters.Entity2OtherField)); if (searchParameters.Entity2OtherField2.HasValue) query = query.Where(u =&gt; u.Entity2List.Any(e =&gt; e.Event.Entity2OtherField2 == searchParameters.Entity2OtherField2)); if (searchParameters.Active.HasValue) query = query.Where(e =&gt; e.Active == searchParameters.Active.Value); return this.GetPageByStartEntity(pageSize.Value, startEntity.Value, query, sortOrder, sortDesc); } </code></pre> <p>The problem with this is that for every time I add on another where that checks the child of Entity1 (Entity2) for a certain field, it takes on a new " AND EXISTS" clause to the sql statement generated, so that it is doing an exists and checking table Entity2 all over again for every different field checked, rather than doing a single EXISTS on Entity in the query, and checking all fields I tacked on to the query (i.e. EntityOtherField1 and EntityOtherField2). I haven't been able to find a better way to do a search based on user inputs than constantly checking for the input being there (add to the search parameters)) and then tacking on a new where to the current query. Can anyone tell me if there is a better way to do this? Thanks!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload