I'm working on an api endpoint that takes optional search parameters. This is sort of complicated because of the table structure. I'm trying to figure out the best and most efficient approach to filtering the data. Bear with me, the data structure is much more complex but I'm simplifying the entity objects (see below). Because I can only do this in linq to sql/entities and not a stored procedure (client rules). The challenge is applying the filters on certain levels of the entity data, not just the top level. For example if I wanted to filter on Stream Id
, where does the where clause go? Again, I've just never had a scenario like this, while using linq to entities and so I'm very curious on the best approaches, if any. Would I make 3 separate predicate builders to achieve this? Looking forward to feedback!
EDIT To be clear I was thinking along the lines of having a different predicate per filter like below and then inserting the predicate in it's corresponding Where clause.
var p = PredicateBuilder.New<Library>();
if (request.LibraryId > 0)
p = p.And(x => x.LibraryId == request.LibraryId);
if (!string.IsNullOrEmpty(request.LibraryName))
p = p.And(x => x.LibraryName!.Contains(request.LibraryName));
Then using .Where(p)
but never tried it this way in multiple places.
Sample query
var libraries = DBContext.Library!.DefaultIfEmpty()!
.Include(x => x.Version)!
.ThenInclude(v => v.Emission)!
.ThenInclude(e => e.StreamEmission)!
.ThenInclude(se => se!.Stream)!
//.WHERE(predicate1)**
//filter on library Id, Library Name
.Select(lm => new LibraryViewModel
{
LibraryId = lm.LibraryId,
LibraryName = lm.LibraryName,
Versions = lm.Versions!
//.WHERE(predicate2)
//filter on Version Id, Version Name, Create Date
.Select(vm => new VersionViewModel
{
VersionId = vm.VersionId,
VersionName = vm.VersionName,
CreateDate = vm.CreateDate,
Streams = vm.Emission!
.Select(vdse => vdse.StreamEmission)
.Select(ds => ds!Stream)
//.WHERE(predicate3)
//filter on Stream Id or Stream Name
.GroupBy(dse => new { dse!.StreamId, dse.StreamName })
.Select(sm => new StreamViewModel
{
StreamId = sm!.Key.StreamId,
StreamName = sm.Key.StreamName,
}).ToList()
}).ToList()
});
Sample json response
[{
"LibraryId" : 24,
"LibraryName" : "Library 24",
"Versions" : [
{
"VersionId" : 30,
"VersionName : "Version 30",
"CreateDate" : "12/1/2019",
"Streams" : [
{
"StreamId" : 1,
"StreamName" : "Stream Name 1"
},
{
"StreamId" : 2,
"StreamName" : "Stream Name 2"
}]
}]
}],
Entity Objects
public class Library
{
public int LibraryId {get;set;}
public string LibraryName {get;set;}
//navigation property
public ICollection<Version> Versions {get;set;}
}
public class Version
{
public int VersionId {get;set;}
public string VersionName {get;set;}
public DataTime CreateDate {get;set;}
//navigation property
public ICollection<Emission> Emissions {get;set;}
//navigation property
public Library? Library {get;set;}
}
//join table/class between Version and StreamEmission tables
public class Emission
{
public int EmissionId {get;set;}
public string Name {get;set;}
//emission foreign key
public StreamEmissionId {get;set;}
//navigation property
public StreamEmission StreamEmission {get;set;}
public int VersionId {get;set;}
//navigation property
public Version? Version {get;set;}
}
//join table between Stream and Emission tables
public class StreamEmission
{
public int StreamEmissionId {get;set;}
public int StreamId {get;set;}
//navigation property
public Stream Stream {get;set;}
}
public class Stream
{
public int StreamId {get;set;}
public string StreamName {get;set;}
//navigation property
public ICollection<StreamEmission> StreamEmissions {get;set;}
}