C# Lambda - EF Core, Col A+ Col B

Question

I'm looking for a way to select using lambda.. something similar to this TSQL

SELECT Id FROM TableA 
    WHERE TableA.col1 +' '+ TableA.col2 IN 
        (
            SELECT TableB.col1 +' '+ TableB.col2 FROM TableB
        )

so far I have this lambda expression:

public async Task<int[]> GetFavouritesIdsAsync()
{
    String[] mergedTableB = await _basicContext.TableB.Select(x => $"{x.col1}_{x.col2}").ToArrayAsync(); // this works!
    return await _basicContext.TableA.Where(x => mergedTableB.Contains($"{x.col1}_{x.col2}")).Select(x => x.Id).ToArrayAsync(); 
}

but it doesn't work - EF couldn't translate string.format bla bla....

any workaround?

Why? you may use inner join on A.1 = B.1 and A.2 = B.2 and get the same results — Selvin, Jul 15 '20 at 10:46
String concatenation is [not sargable](https://stackoverflow.com/q/29406797/87698). I'm not terribly familiar with Entity Framework, but according to [this question, using `Any` should work to compare multiple columns](https://stackoverflow.com/q/6912733/87698). — Heinzi, Jul 15 '20 at 10:53

score 1 · Accepted Answer · answered Jul 15 '20 at 10:53

1

Using LINQ, using simple join operation:

_basicContext.TableA.Join(
    _basicContext.TableB,
    a => new {a.col1, a.col2},
    b => new {b.col1, b.col2},
    (a, b) => a );

Workaround to existing attempt:

mergedTableB.Contains(x.col1 + "_" + x.col2)

answered Jul 15 '20 at 10:53

Michał Turczyn

32,028
14
47
69

C# Lambda - EF Core, Col A+ Col B

1 Answers1