Creating dynamic queries with entity framework
You could compose an IQueryable<T> step by step. Assuming you have a FilterDefinition class which describes how the user wants to filter ...
public class FilterDefinition
{
public bool FilterByName { get; set; }
public string NameFrom { get; set; }
public string NameTo { get; set; }
public bool FilterByQuantity { get; set; }
public double QuantityFrom { get; set; }
public double QuantityTo { get; set; }
}
... then you could build a query like so:
public IQueryable<SomeEntity> GetQuery(FilterDefinition filter)
{
IQueryable<SomeEntity> query = context.Set<SomeEntity>();
// assuming that you return all records when nothing is specified in the filter
if (filter.FilterByName)
query = query.Where(t =>
t.Name >= filter.NameFrom && t.Name <= filter.NameTo);
if (filter.FilterByQuantity)
query = query.Where(t =>
t.Quantity >= filter.QuantityFrom && t.Quantity <= filter.QuantityTo);
return query;
}
Dynamic query with OR conditions in Entity Framework
You're probably looking for something like Predicate Builder which allows you to control the AND's and OR's of the where statement easier.
There's also Dynamic Linq which allows you to submit the WHERE clause like a SQL string and it will parse it into the correct predicate for a WHERE.
How can I build Entity Framework queries dynamically?
You can easily use query compossition.
You first start with query.
IQueryable<Log> query = context.LogSet;
They you compose sub-queries.
if (logType == LogType.Disk)
{
query = query.OfType<DiskLog>(); // not sure if you need conversion here
}
else if (logType == LogType.Network)
{
query = query.OfType<NetworkLog>(); // not sure if you need conversion here
}
query = query.Where(x => x.Computer.User.UserKey == userKey);
if (computerId != 0)
query = query.Where( x => x.Computer.Id == computerId);
// .. and so on
query = query.OrderByDescending(x => x.Id).Skip(nSkip).Take(nTake);
return query.ToList(); // do database call, materialize the data and return;
And I would recomend using nullable value types for cases, when there is no value.
Entity Framework 6: Dynamically build search clause against DB
I assume you are able to build a sub-condition from each line in your GUI. For example, the first line would be similar to
string userInputName = "john";
Expression<Func<Person, bool>> condition1 = person => person.Name.Contains(userInputName);
And can be used as
var selection = db.Persons.Where(condition1).ToList();
For multiple sub-conditions, and is just a concatenation of Where conditions:
var conditions = new List<Expression<Func<Person, bool>>>() { condition1, condition2, ... };
IQueryable<Person> query = db.Persons;
foreach (var condition in conditions)
{
query = query.Where(condition);
}
var selection = query.ToList();
The little trickier part comes with the or conditions. Suppose you have normalized your conditions into groups of alternative and conditions (disjunctive normal form), then you get multiple groups of valid results. For the sake of simplicity, I keep it at 2 groups here, but it can be generalized the same way as the and conditions:
The or can be represented by the Union operation on sub-queries.
var conditions1 = new List<Expression<Func<Person, bool>>>() { condition1, condition2, ... };
IQueryable<Person> query1 = // see above construction logic for 'and' conditions
var conditions2 = new List<Expression<Func<Person, bool>>>() { condition5, condition6, ... };
IQueryable<Person> query2 = // see above construction logic for 'and' conditions
IQueryable<Person> query = query1.Union(query2);
var selection = query.ToList();
A few last words: consider using some established filter/search frameworks instead. This approach is probably neither the prettiest nor the fastest you can find.
As requested, a small example with some in-memory data. Note this is not 100% equivalent to Linq-to-entities. For example, string comparison will deal with upper-lower case differently and maybe not every kind of condition is allowed for SQL.
public enum TypeOfContact
{
Unknown,
Email
}
public class Person
{
public string Name { get; set; }
public DateTime Birth { get; set; }
public TypeOfContact ContactType { get; set; }
public string ContactValue { get; set; }
}
public class Program
{
static void Main(string[] args)
{
// test data to simulate a database table
var Persons = new List<Person>
{
// + All conditions met
new Person { Name = "john doe", Birth = new DateTime(2011, 1, 1), ContactType = TypeOfContact.Email, ContactValue = "john.doe@email.com" },
// - Not in result
new Person { Name = "danny doe", Birth = new DateTime(2012, 1, 1), ContactType = TypeOfContact.Email, ContactValue = "johndoe@hotmail.com" },
// + Name contains john
new Person { Name = "john doe", Birth = new DateTime(2013, 1, 1), ContactType = TypeOfContact.Unknown, ContactValue = "" },
// + Birth, ContactType and ContactValue correct
new Person { Name = "justin", Birth = new DateTime(2014, 1, 1), ContactType = TypeOfContact.Email, ContactValue = "slenderman@email.com" },
// - Not in result because Name and Birth are wrong
new Person { Name = "jonny", Birth = new DateTime(1979, 1, 1), ContactType = TypeOfContact.Email, ContactValue = "you-know-who@email.com" },
// - Not in result
new Person { Name = "jenny doe", Birth = new DateTime(2016, 1, 1), ContactType = TypeOfContact.Unknown, ContactValue = "" },
}.AsQueryable();
// single-line-conditions
Expression<Func<Person, bool>> c1 = p => p.Name.Contains("john");
Expression<Func<Person, bool>> c2 = p => p.Birth.Date >= new DateTime(1980, 1, 1);
Expression<Func<Person, bool>> c3 = p => p.ContactType == TypeOfContact.Email;
Expression<Func<Person, bool>> c4 = p => p.ContactValue.EndsWith("@email.com");
// DNF groups: outer list = or; inner list = and
// c1 or (c2 and c3 and c4)
var conditionList = new List<List<Expression<Func<Person, bool>>>>
{
new List<Expression<Func<Person, bool>>>
{
c1,
},
new List<Expression<Func<Person, bool>>>
{
c2,
c3,
c4,
},
};
var andSubResults = new List<IQueryable<Person>>();
foreach (var andQueries in conditionList)
{
var subQuery = Persons;
foreach (var andQuery in andQueries)
{
subQuery = subQuery.Where(andQuery);
}
andSubResults.Add(subQuery);
}
var query = andSubResults.FirstOrDefault();
foreach (var subResult in andSubResults.Skip(1))
{
query = query.Union(subResult);
}
var selection = query.ToList();
// just check the result in debugger
}
}
Building dynamic where query from linq to ef core
Expanding on the idea from answer you pointed to is not that complicated.
You need to change assumption so the property name in SerchField can hold definition of property path like Company.City or Company.Country.Name as well as just property FirstName. And you need to handle it:
so instead of just having simple property like so:
Expression columnNameProperty = Expression.Property(pe, fieldItem.Name);
you need to handle property chain:
Expression columnNameProperty = GetPropertyExpression(pe, fieldItem.Name);
Expression GetPropertyExpression(Expression pe, string chain){
var properties = chain.Split('.');
foreach(var property in properties)
pe = Expression.Property(pe, property);
return pe;
}
Basically what this code does it applies properties over previous modifying pe variable on every loop creating property chain. Entity framework will process it and create appropriate joins. This will work only for single relations and not for collections.
so the modified code from that answer looks like this:
public class FilterLinq<T>
{
Expression GetPropertyExpression(Expression pe, string chain)
{
var properties = chain.Split('.');
foreach(var property in properties)
pe = Expression.Property(pe, property);
return pe;
}
public static Expression<Func<T, Boolean>> GetWherePredicate(params SearchField[] SearchFieldList)
{
//the 'IN' parameter for expression ie T=> condition
ParameterExpression pe = Expression.Parameter(typeof(T), typeof(T).Name);
//combine them with and 1=1 Like no expression
Expression combined = null;
if (SearchFieldList != null)
{
foreach (var fieldItem in SearchFieldList)
{
//Expression for accessing Fields name property
Expression columnNameProperty = GetPropertyExpression(pe, fieldItem.Name);
//the name constant to match
Expression columnValue = Expression.Constant(fieldItem.Value);
//the first expression: PatientantLastName = ?
Expression e1 = Expression.Equal(columnNameProperty, columnValue);
if (combined == null)
{
combined = e1;
}
else
{
combined = Expression.And(combined, e1);
}
}
}
//create and return the predicate
return Expression.Lambda<Func<T, Boolean>>(combined, new ParameterExpression[] { pe });
}
}
Creating dynamic queries with Entity Framework across multiple tables?
Your query looks "symmetric" to me with respect to people and addresses, it only gets "asymmetric" in the final projected result. So, my idea is to express this symmetry in the query as far as possible:
Get a set (
IQueryable<Address>, not executed at once) of addresses filtered by street name and street numberGet a set (
IQueryable<Person>, not executed at once) of people filtered by the beginning of first name and last nameJoin the two sets by
AddressMap_ID. The resulting set of people and addresses contains only those pairs that fulfill the filter criteria for addresses and people. If one of the filter criteria for person or address is not supplied (the first and the third of your examples at the bottom of the question) the join happens on the unfiltered set of all people/addresses, i.e. the joined pairs contain always all people of the filtered address (or all addresses of the filtered people)Group the joined pairs of people and addresses by
Address.IDProject the groups into your
Resultscollection. The group key is theAddressID.StreetNameandStreetNumbercan be fetched from the first address in each group and thepeopleare projected from the people in each group.Execute the query
The following code doesn't cover the case specifically that none of the four filter criteria is supplied. It works in that case but would just load all addresses with all people of those addresses. Maybe you want to throw an exception in that case. Or return nothing (model.Results = null or so), then just jump out of the method.
public void GetResults(Search model)
{
using (var _context = new Entities())
{
// "All" addresses
IQueryable<Address> addresses = _context.Addresses;
// "All" people
IQueryable<Person> people = _context.People;
// Build a Queryable with filtered Addresses
if (!string.IsNullOrEmpty(model.streetname))
addresses = addresses.Where(a => a.StreetName
.StartsWith(model.streetname));
if (!string.IsNullOrEmpty(model.streetnumber))
addresses = addresses.Where(a => a.StreetNumber
.StartsWith(model.streetnumber));
// Build a Queryable with filtered People
if (!string.IsNullOrEmpty(model.fname))
people = people.Where(p => p.FirstName == model.fname);
if (!string.IsNullOrEmpty(model.lname))
people = people.Where(p => p.LastName == model.lname);
// Join the two Queryables with AddressMap_ID
// and build group by Address.ID containing pairs of address and person
// and project the groups into the Results collection
var resultQuery = from a in addresses
join p in people
on a.AddressMap_ID equals p.AddressMap_ID
group new { a, p } by a.ID into g
select new Results
{
AddressID = g.Key,
StreetName = g.Select(ap => ap.a.StreetName)
.FirstOrDefault(),
StreetNumber = g.Select(ap => ap.a.StreetNumber)
.FirstOrDefault(),
people = g.Select(ap => new PeopleResults
{
First = ap.p.FirstName,
Last = ap.p.LastName
})
};
// Execute query (the whole code performs one single query)
model.results = resultQuery.ToList();
}
}
I am unsure if I interpret the AddressMap table correctly as a kind of join table for a many-to-many relationship (Address can have many people, Person can have many addresses), but the code above yields the three results of the three queries in your example as expected if the tables are filled like so:
The AddressMap table isn't actually used in the query because Addresses and People table are joined directly via the AddressMap_ID columns.
Related Topics
Does List<T> Guarantee Insertion Order
Client Side Groupby Is Not Supported
Creating Wcf Channelfactory<T>
Why Does Resharper Want to Use 'Var' for Everything
Why Can't C# Interfaces Contain Fields
How to Flatten Nested Objects with Linq Expression
Why Does the Contains() Operator Degrade Entity Framework's Performance So Dramatically
Cannot Set Some Http Headers When Using System.Net.Webrequest
Mirroring Console Output to a File
Linq Select Objects in List Where Exists in (A,B,C)
Filter/Search Using Multiple Fields - ASP.NET MVC
How to Compare Only Date Without Time in Datetime Types in Linq to SQL with Entity Framework
How to Specify Proxy Credentials in Your Web.Config
Compare Equality Between Two Objects in Nunit