How do I add a where filter using the original Linq-to-SQL object in the following scenario

Question

I am performing a select query using the following Linq expression:

Table<Tbl_Movement> movements = context.Tbl_Movement;
var query = from m in movements
            select new MovementSummary
            {
                Id = m.DocketId,
                Created = m.DateTimeStamp,
                CreatedBy = m.Tbl_User.FullName,
                DocketNumber = m.DocketNumber,
                DocketTypeDescription = m.Ref_DocketType.DocketType,
                DocketTypeId = m.DocketTypeId,
                Site = new Site()
                {
                    Id = m.Tbl_Site.SiteId,
                    FirstLine = m.Tbl_Site.FirstLine,
                    Postcode = m.Tbl_Site.Postcode,
                    SiteName = m.Tbl_Site.SiteName,
                    TownCity = m.Tbl_Site.TownCity,
                    Brewery = new Brewery()
                    {
                        Id = m.Tbl_Site.Ref_Brewery.BreweryId,
                        BreweryName = m.Tbl_Site.Ref_Brewery.BreweryName
                    },
                    Region = new Region()
                    {
                        Description = m.Tbl_Site.Ref_Region.Description,
                        Id = m.Tbl_Site.Ref_Region.RegionId
                    }
                }
            };

I am also passing in an IFilter class into the method where this select is performed.

public interface IJobFilter
{
    int? PersonId { get; set; }
    int? RegionId { get; set; }
    int? SiteId { get; set; }
    int? AssetId { get; set; }
}

How do I add these where parameters into my SQL expression? Preferably I'd like this done in another method as the filtering will be re-used across multiple repositories.

Unfortunately when I do query.Where it has become an IQueryable<MovementSummary>. I'm assuming it has become this as I'm returning an IEnumerable<MovementSummary>. I've only just started learning LINQ, so be gentle.

Answer:

private IQueryable<Tbl_Docket> BuildQuery(IQueryable<Tbl_Docket> movements, IMovementFilter filter)
{
   if (filter != null)
   {
      if (filter.PersonId.HasValue) movements = movements.Where(m => m.UserId == filter.PersonId);
      if (filter.SiteId.HasValue) ...
   }
   return movements;
}

Which is called like follows:

var query = from m in this.BuildQuery(movements, filter)
            select new... {}

Answer 1

Hey GTT--

You have to call the where statement before you fire your select statement, e.g.:

IQueryable<Tbl_Movement> movements = context.Tbl_Movement;
if (filter != null)
{
   if (filter.PersonId != null) movements = movements.Where(m => m....PersonId == filter.PersonId);
   if (filter.RegionId != null) movements = movements.Where(m => m....RegionId == filter.RegionId);
   if (filter.SiteId != null) movements = movements.Where(m => m...SiteId == filter.SiteId);
   if (filter.AssetId != null) movements = movements.Where(m => m...AssetId == filter.AssetId);
}

 var query = m from movements...

As opposed to using this IFilter class, you might want to consider a Fluent Pipe-based Repository structure, e.g.:

var movements = new MovementsPipe()
    .FindSiteId(1)
    .FindAssetIds(1, 2, 3)
    .FindRegionId(m => m > 10)
    .ToMovementSummaryList();

Hope this helps. Let me know if you have any questions.