MostlyLucid MostlyLucid - 2 months ago 7
C# Question

Linq to entities to return a list that contains elements from another list

I have tried repeated different things to do this.
I need to query a database and return only the records that have a date that is contained in a List - like a dynamic "Where" statement.
I am sure it will involve => but cannot get the syntax correct.

I am building a short list below to test, but it could have any amount of items.

Below needs to return just the records where total.date_reference is contained in datesToShow.

List<DateTime> datesToShow = new List<DateTime>();
datesToShow.Add(new DateTime(2016, 9, 22));
datesToShow.Add(new DateTime(2016, 9, 21));

var todays_totals = (from total in dbContext.daily_totals
select new
{
total.customer.customer_name,
total.date_reference,
total.EDI_PODs_sent,
total.open_stops,
total.total_pieces,
total.new_stops,
total.total_weight,
total.Unsent_PODs_released,
total.Unsent_PODs_unreleased,
total.last_updated
}).ToArray();


If I do this:

var todays_totals = (from total in dbContext.daily_totals
select new
{
total.customer.customer_name,
total.date_reference,
total.EDI_PODs_sent,
total.open_stops,
total.total_pieces,
total.new_stops,
total.total_weight,
total.Unsent_PODs_released,
total.Unsent_PODs_unreleased,
total.last_updated
}).Where(el => datesToShow.Contains(el.date_reference)).ToArray();


I get a " Unknown method Where(?)..."
I have tried using both a list and a array like:

DateTime[] datesToShow = new DateTime[]
{
new DateTime (2016,9,22),
new DateTime (2016,9,23)
};


I would also be fine with a new result set that is a subset of todays_totals.
something like the below (where I actually started from)

var newList = (from t in todays_totals where (t=>datesToShow.Contains(t.date_reference))).ToArray();

Answer

You can try using Contains extension method:

     var todays_totals = (from total in dbContext.daily_totals
                          where datesToShow.Contains(DbFunctions.TruncateTime(total.date_reference))
                          select new
                         {
                             total.customer.customer_name,
                             total.date_reference,
                             total.EDI_PODs_sent,
                             total.open_stops,
                             total.total_pieces,
                             total.new_stops,
                             total.total_weight,
                             total.Unsent_PODs_released,
                             total.Unsent_PODs_unreleased,
                             total.last_updated
                         }).ToArray();

DbFunction.TruncateTime will help you to clean your dates in case they come with time.

Comments