0

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;}
}
joby-flick
  • 1,790
  • 1
  • 12
  • 21
  • Use a dynamic where clause based on conditions being passed in. Start with an IQueryable and your includes. Then based on your conditions build your where clause (ie: libraries += libraries.Where(w => w. LibraryId == myId);). At the end, call your db (ie: FirstOrDefaultAsync, To ListAsync, etc...) – GH DevOps Dec 19 '22 at 17:12
  • PredicateBuilder is the newer way of doing that instead of filtering the query over and over depending on how my filters are passed in. It's more about if it's even possible to have more than one predicate depending on the level or "section" of data that needs to be filtered. – joby-flick Dec 19 '22 at 17:22
  • Only one filter with IQueryable! – GH DevOps Dec 19 '22 at 17:25
  • I understand but the filters are on different navigation properties! – joby-flick Dec 19 '22 at 17:44

0 Answers0