1

I have a SQL Server table like this:

Id (int, primary key)
UserId (int)
SomeDate (Date)
JsonData (nvarchar)

In C# I have a collection resembling the type

List<(int UserId, DateTime SomeDate)>

How do I find all database entries matching the two fields from my C# collection? I would like to make one database call and not iterate list and search for them one by one.

For example if the C# collection has 2 entries:

(1, '2020-01-02'), (5, '2020-01-01')

I would like to find any matching database entries. Something like (not real code)

context.DatabaseTable
      .Where(e => (e.UserId == 1 && e.SomeDate == '2020-01-02') ||  
            (e.UserId == 5 && e.SomeDate == '2020-01-01') ||
            ... as many conditions as collection has elements...)
user1713059
  • 1,425
  • 2
  • 17
  • 34

1 Answers1

1

So you have a table with Users, where every User has at least properties Id and SomeDate. You also have a sequence of combinations of {UserId, Date}, and you want to query all Users that have a value for {Id, SomeDate} that equals at least one of the values in your collection.

List<(int UserId, DateTime SomeDate)> collection = ...

var valuesToCompare = colection.Select(element => new
{
    Id = element.UserId,
    Date = element.SomeDate,
});
var users = dbContext.Users.Where(user => valuesToCompare.Contains(new 
    {
        Id = user.Id,
        Date = user.SomeDate,
    }));

I'm not really familiar with the new (int UserId, DateTime SomeDate) type. If it is possible to convert a User to a new (int UserId, DateTime SomeDate), then you won't have to do the first Select, a Contains will do.

Harald Coppoolse
  • 28,834
  • 7
  • 67
  • 116
  • FYI C#7 introduced the `ValueTuple` types. The syntax you are using is the old Anonymous type syntax. – Aron Apr 19 '21 at 09:33
  • Linq Expressions are unable to work with `ValueTuples` because they require method attributes to help with giving "names" to each Tuple value, which Linq Expressions do not support. – Aron Apr 19 '21 at 09:35
  • Does not work for me: "The LINQ expression 'DbSet() .Where(u => __valuesToCompare_0.Contains(new { Id = u.Id, Date = u.SomeDate }))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information." – user1713059 Apr 21 '21 at 08:27