Ternary operator in linq to SQL select

Question

0.00/5 (No votes)

See more:

I think it should be something really simple, but cannot figure it out. I have a simple select query:

.Select(company => company.clients
    .Select(client => client.policy != null ? new
    {
        name = client.name,
        policy = client.policy,
    } : new
    {
       name = client.name,
       policy = company.policy,
    }))

Company table has clients table, each client can have a policy, and company can have a policy. If client has a policy object, I need to select it, but if it does not, I need company's policy object.

What I have tried:

The above code fails with error:

An unhandled exception has occurred while executing the request.
     System.InvalidOperationException: Processing of the LINQ expression '(ProjectionBindingExpression: Inner)' by 'RelationalProjectionBindingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

What am I doing wrong?

Posted 24-Nov-20 5:13am

csrss

Updated 24-Nov-20 23:27pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2020-11-24T05:25:00

Solution 1

When you write a LINQ query, you have to keep in mind that the query is not executed on the client machine. It it translated to SQL query language, then that query is sent to the server for execution and the result set returned.

The problem is there is no conversion of the C# ternary operator to an SQL WHEN in the context you're using it.

Posted 24-Nov-20 5:25am

Dave Kreskowiak

Comments

csrss 24-Nov-20 11:27am

Yes, I understand it cannot be translated, and that it runs on server, but there should be some way to do it like this?

Dave Kreskowiak 24-Nov-20 11:37am

I'm no LINQ expert, but since you're just changing one field in the result, you could try putting the ternary on just the one field:

.Select(company => company.clients
    .Select(client => client.policy != null ? new
    {
        name = client.name,
        policy = client.policy != null ? client.policy : company.policy
    }))

csrss 24-Nov-20 11:49am

Yes, tried to do it that way, same result.

Dave Kreskowiak 24-Nov-20 11:53am

Than, I couldn't tell you. Like I said, I'm no expert in LINQ.

csrss 24-Nov-20 11:54am

Yeah, I understand. Thanks anyway. The more I am trying to do with this linq to sql - it is just crap and nothing else. Cannot translate the simplest queries. It can just perform Where() couple of times, some select and that's it. Nothing beyond.

Richard Deeming · Answer 2 · 2020-11-24T23:27:00

Solution 2

It depends on what shape you want for the returned data. I suspect the problem may be with the nested Select. Try:

C#

.SelectMany(company => company.clients.Select(client => new
{
    name = client.name,
    policy = client.policy != null ? client.policy : company.policy,
}))

Alternatively, you could return both properties and then select the correct one in your client code:

C#

.SelectMany(company => company.clients.Select(client => new
{
    client.name,
    client.policy,
    company_policy = company.policy,
}))
.AsEnumerable()
.Select(client => new
{
    client.name,
    policy = client.policy ?? client.company_policy,
});

Posted 24-Nov-20 23:27pm

Richard Deeming

Comments

csrss 25-Nov-20 5:34am

First one does not work. Second one, cannot be done, because I need to run a query on selected property. So, my scenario is this: if client has a policy - take, if not - take company policy and then run a query for all rules contained within policy object, so this is .Where(...) on a result, returned by ternary operator and then there are next steps, and all this should be executed within one request to database, and all is Linq to sql. Client side execution is forbidden.

Richard Deeming 25-Nov-20 5:48am

Assuming this is EF Core, as mentioned in the error message - remember, Linq2SQL was a different and long-discontinued product - the ternary operator should work.

Which version are you using?

csrss 25-Nov-20 6:04am

My setup is this: .NET 5.0, ef core 3.1.

Richard Deeming 25-Nov-20 6:19am

Odd - a ternary expression works fine for me in EF Core 3.1 on .NET Framework 4.8:

db.Customers.Select(c => new
{
    c.Name,
    Currency = c.Currency == null ? c.Firm.Currency : c.Currency,
})

The null-coalescing operator also works:

db.Customers.Select(c => new
{
    c.Name,
    Currency = c.Currency ?? c.Firm.Currency,
})

Maybe there's another problem with your entities or your query?

BTW, if you're on .NET 5, you could update to EF Core 5, which was released a couple of weeks ago:
Announcing the Release of EF Core 5.0 | .NET Blog[^]

csrss 25-Nov-20 6:26am

Yeah, if I update to ef core 5, a lot of queries stop working or are a lot slower, so this is why I stick to 3.1. This is strange, I have very similar thing like you do, then I do something like this:
db.Customers.Select(c => new
{
c.Name,
Currency = c.Currency ?? c.Firm.Currency,
}).Select(x => new { a = x.Currency.SomeListProperty.Where(...)})
And everything crashes. Everything is initialised with huge .Include().IncludeThen() etc