Get distinct results from database that has a "dictionary"

Question

I have a database with a "dictionary" like this:

Id (INT) | Key (VARCHAR) | Value (VALUE) | ForeignKey (INT?)
------------------------------------------------------------
1        | foo           | bar           | 23
2        | bar           | foo           | NULL
3        | foobar        | value         | NULL
4        | foobar        | othervalue    | 47

Now I want to get all keys and values for a specific foreign key, but also all foreign keys that are NULL, so that the result looks like this:

ForeignKey = 23:           ForeignKey = 47:

foo    | bar               bar    | foo
bar    | foo               foobar | othervalue
foobar | value

Originally I tried this approach:

dbContext.Table
    .Where(t => t.ForeignKey == 47 || t.ForeignKey == null)

but that gives me foobar twice.

I then though about multiple requests and unionizing the results:

var t1 = _dbContext.Table
    .Where(t => t.ForeignKey == 47);

var t2 = _dbContext.Table
    .Where(t => t.ForeignKey == null && !t1.Any(tt => tt.Key == t.Key));

var final = t1.Union(t2);

This seemingly works, but it creates this SQL (roughly) which seems excessive with three SELECT, three WHERE and a UNION

SELECT [t1].[Id], [t1].[Key], [t1].[Value], [t1].ForeignKey
FROM [Table] AS [t1]
WHERE [t1].[ForeignKey] = 47
UNION
SELECT [t2].[Id], [t2].[Key], [t2].[Value], [t2].ForeignKey
FROM [Table] AS [t2]
WHERE [t2].[ForeignKey] IS NULL AND NOT (EXISTS (
    SELECT 1
    FROM [Table] AS [t3]
    WHERE ([t3].[ForeignKey] = 47) AND ([t3].[Key] = [t2].[Key])))

I just have a gut feeling that "there's got to be a better way"... so, is there? How can I get keys and values for a specific foreign key, but also keys not already fetched that are NULL?

For "ForeignKey = 47" you should also have "foobar | value"... — The Impaler, Apr 20 '20 at 17:57
@TheImpaler No, that's the point... for "ForeignKey = 47" I want the row where value = "othervalue" — TheHvidsten, Apr 20 '20 at 18:01
"...but also all foreign keys that are NULL..." -- you said that, so "foobar | value" sould be included. What am I reading wrong? — The Impaler, Apr 20 '20 at 18:09
The way I'm understanding this, the items with a null foreign key represent default values. If a row with a key exists for a given foreign ID, that row's value takes precedence. But in the absence of an overriding value for that key, OP wants the result to include that key and its default value in the results. — StriplingWarrior, Apr 20 '20 at 18:28
@StriplingWarrior That would make sense. Unfortunately, I can't really get it. Voting to close after 30 minutes with no clarification. — The Impaler, Apr 20 '20 at 18:41
@StriplingWarrior Your assumptions are correct... if a key appears multiple times, choose the one where ForeignKey equals the one you want (47), otherwise (NULL) return the "default" value... that's why for ForeignKey = 23, foobar = value (default value), but for ForeignKey = 47, foobar = othervalue (specific value overrides default value) — TheHvidsten, Apr 20 '20 at 20:18

score 1 · Accepted Answer · answered Apr 20 '20 at 18:23

1

The SQL you're seeing doesn't seem excessive to me. Unless you've tested this and found it to have unacceptably bad performance, I wouldn't necessarily spend too much time trying to optimize it.

Here's an alternative approach, though, which may be preferable. You'd have to do some testing to see if it yields better SQL, execution plan, and performance. Mostly I'd go with whichever approach will be easier for other developers to understand.

var final = dbContext.Table.Where(t => t.ForeignKey == 47 || t.ForeignKey == null)
    .GroupBy(t => t.Key)
    .Select(g => g.OrderBy(t => t.ForeignKey == null ? 1 : 0).FirstOrDefault());

answered Apr 20 '20 at 18:23

StriplingWarrior

151,543
27
246
315

I liked the idea of your query. I'd tried something similar before but didn't get it working. Unfortunately, your query doesn't work either but yields the following exception: `The LINQ expression '(GroupByShaperExpression:\r\nKeySelector: (c.Key), \r\nElementSelector:(EntityShaperExpression: \r\n EntityType: Table\r\n ValueBufferExpression: \r\n (ProjectionBindingExpression: EmptyProjectionMember)\r\n IsNullable: False\r\n)\r\n)\r\n .OrderBy(t => t.ForeignKey == null ? 1 : 0)' could not be translated` – TheHvidsten Apr 21 '20 at 07:50
Yeah, based on the comments [here](https://stackoverflow.com/a/16274992/120955) it sounds like different versions of EF handle this differently, and none of them probably handle it in an ideal way. I'd probably stick with the query you've got unless performance is causing problems. – StriplingWarrior Apr 21 '20 at 15:30
I managed to optimize my original query and ended up avoiding some `LEFT JOIN` that was generated. Based on the lack of feedback on this issue, and it seeming to go into being opinion-based I'll accept your answer and leave it at that. Thanks for the input. – TheHvidsten Apr 21 '20 at 16:48

Get distinct results from database that has a "dictionary"

1 Answers1