CODEWITHSUNDEEP
c#linqsqliteentity-framework-6
Matt
Matt

Reputation: 1786

Build multiple where clauses with linq

I'm writing a dotnet core MVC web app with EntityFrameWorkCore and Sqlite.

I'm trying to build a dynamic where clause based on data provided by the query string, there could be multiple key value pairs passed in the query string and I want to add them all to the where clause, I thought I could do it like this but it returns me all rows from the initial query.

public class Application
{
    public Application()
    {
        Data = new List<Data>();
    }
    public int ApplicationId { get; set; }
    [Required][Display(Name = "Application Name")]
    public string Name { get; set; }
    public Guid PublicKey { get; set; }
    public Guid PrivateKey { get; set; }
    public bool HideFromSearch { get; set; }
    public DateTime InsertDate { get; set; }
    public List<Data> Data { get; set; }
}

public class Data
{
    public Data()
    {
        DataItems = new List<DataItem>();
    }
    public int DataId { get; set; }
    public int ApplicationId { get; set; }
    public DateTime InsertDate { get; set; }
    public List<DataItem> DataItems { get; set; }
}

public class DataItem
{
    public int DataItemId { get; set; }
    public int DataId { get; set; }
    public string PropertyName { get; set; }
    public string PropertyValue { get; set; }
}

Where code

var apps = context.Applications.Include(app => app.Data).ThenInclude(data => data.DataItems).Where(app => app.PublicKey == publicKey);

foreach (var item in Request.Query)
{
    apps = apps.Where(q => q.Data.Any(r => r.DataItems.Any(s => s.PropertyName == item.Key && s.PropertyValue == item.Value[0] )));
}

Upvotes: 1

Views: 605

Answers (1)

Steven J
Steven J

Reputation: 316

I used Dynamic Linq in a project and think it is what you are looking for to solve complex dynamic queries.

Scott Guthrie sample

Nuget package

Upvotes: 1

Related Questions