3

I have a collection (local array, filled from elsewhere) with a set of Section and Lines. These are NOT primary keys.

Say something like this:

class SectionLine {
  int Section { get; }
  int Line { get; }
}
class MyEntity {
  int EntityId { get; set; }
  int Section { get; set; }
  int Line { get; set; }
  string OtherProperty { get, set; } 
  // Many other options...
}
// ...
SectionLine[] allSectionLines = new [] { new SectionLine { Section = 5, Line = 2 }, new SectionLine { Section = 5, Line = 3 }, // etc.

So I want to filter my collection on the DbContext for those that match -both- section and line from any in the array allSectionLines.

Note that Section and Line on the entity MyEntity are not primary keys, but they do have a multi-column index which would be great if it would be usable.

I can see a couple options but I don't like any... namely:

  • Multiple queries, one for each element in allSectionLines. This array comes from a different source (it's not code-static) and could have many elements, so I don't like this. Edit: This also applies to making a composite predicate with many OR clauses... if the number in the array collection is big enough I'm sure this would perform rather poorly
  • Creating a single property from the multi-property, something like:
var sectionLines = allSectionsLines.Select(x => x.Section + "-" + x.Line);
var myQuery = _dbContext.MyEntities.Where(x => sectionLines.Contains(x.Section + "-" + x.Line));

This works, but it does full table scans and doesn't take the index into account, so I'd prefer to avoid it

  • Create a stored procedure on the database and do some multi-column joins, first inserting the array on a temp table (or passing this table as a parameter): this definitely works but this database is managed by someone else and I'm not doing any kind of migrations. I could definitely ask for the SP to be there... but if it's solvable by code, I'd prefer to do it without "touching" the database schema

And there's another hack I'm doing now (projecting the entities to a simple DTO-like type with just ID, Section and Line, then filter in memory and get the IDs, then query again for those IDs). This works JUST because of the data that is there and the available memory and speed of the actual server, but I do not like it.

Is there another way I haven't found of doing such a query, preferably directly on Entity Framework, which doesn't waste memory, extra "seemingly unneeded" queries, and may use the indexes on the database instead of doing full scans?

This is EF Core 3.1

Addendum: my current line of thought is adding a calculated field (with an index) for the "Section-Line" string and perform the query there... again, I'm not the owner of the database so I'd prefer not "having to ask for it"... but if that's the only way I can actually do it. It's not that it's not solvable... I'm just evaluating alternatives and seeing if I'm missing an obvious one

Jcl
  • 27,696
  • 5
  • 61
  • 92
  • Is `.Where(s => sections.Contains(s.Section) && lines.Contains(s.Line))` an option? Then apply the combined filter in memory? Or would that bring back too much data? – Oliver Jul 14 '20 at 07:42
  • @Oliver that may work, but the index is for both properties (not section and line separately), so doing that would not use the index either and would do a full table scan... I'd rather do a composite ad-hoc "string" (like in the second bullet) to filter by that if that was the case – Jcl Jul 14 '20 at 07:44
  • Do you have a `DbSet` for the sections lines? And have you configured a composite key, with `Section` and `Line`? – Jakob Busk Sørensen Jul 14 '20 at 07:46
  • 1
    Does this answer your question? [EntityFramework - contains query of composite key](https://stackoverflow.com/questions/26198860/entityframework-contains-query-of-composite-key) – Oliver Jul 14 '20 at 07:47
  • @JakobBuskSørensen nope, the SectionLine comes from elsewhere (not the database)... and no, there's no composite key (there is a composite index for the "MyEntity" table on the database, but it's not a key, and not unique) – Jcl Jul 14 '20 at 07:49
  • @Oliver uhm... most of the answers there are points I exposed on my question, but there might be something there that I haven't thought of, let me take a look, thanks! – Jcl Jul 14 '20 at 07:50
  • 1
    @Oliver basically all answers on that post (including the most voted, which has many) expose the "solutions" I'm actually giving here as "don't like". Tough luck, it seems... I'll have to go for one that I don't like, unless someone comes up with something fresh :-) – Jcl Jul 14 '20 at 07:54
  • 1
    @Jcl It's true none of the solutions there appear ideal - however I think that might be the answer based on the conditions you have stated. – Oliver Jul 14 '20 at 07:54

0 Answers0