First off, I'm using Entity Framework Core 5 with SQL Server provider and a repository pattern to do any work related to the infrastructure.
My problem is that I'm trying to query for a list of CardSale entities while matching on a list of parameters.
The CardSale entity has no key currently, it uses a composite clustered index for uniqueness and organization. we went for this kind of index for several reasons including GroupBy query optimization.
So if I want to query for a single record I can just use something simple like this:
return await _appDbContext.CardSales
.Where(c =>
c.PurchaseCurrency == currency &&
c.Date == date &&
c.SubcategoryId == subcategoryId &&
c.CardId == cardId &&
c.RegionId == regionId &&
c.AccountId == accountId)
.Include(c => c.Card)
.Include(c => c.Region)
.Include(c => c.Account)
.FirstOrDefaultAsync();
Now the problem lies in the next step, a portion of the code requires the ability to pass in a list of Notification objects to a query and receive a list of matching CardSale object on the properties mentioned above.
Before the current change in the code base we had a really big string Key property which was just the properties mentioned above interpolated into a single string (I know, it was really bad) so that meant I could pass in a List<string> ids and use something like
return await _appDbContext.CardSales
.Where(c => ids.Contains(c.id)
and it works just fine.
But now my problem is that this big string id doesn't exist anymore, I only have the individual properties and the composite clustered index.
How can I query for multiple CardSale entities while using parameters from multiple Notification objects?
Extra notes:
All ids are Guid. Date is a date component of a DateTime object.
The query for a specific CardSale has to contain ALL the mentioned properties in the first query which all also exist in the Notification object.
