C# Left join linq results in ORA-12704 when selecting a default string value

Question

I'm attempting to perform a left join of two tables. From the second table I only want to select the name. However, if the join couldn't find a corresponding item, I want to select a default string as name. My code:

var query = from order in _context.Orders
            join project in _context.Projects on order.ProjectId equals project.Id
                into orderjoin
            from item in orderjoin.DefaultIfEmpty()
            select
                new ResultModel
                {
                    ProjectName = item.Name ?? "deleted project",
                    ProjectId = order.ProjectId,
                    Origin = order.Origin,
                    Destination = order.Destination,
                    ...
                };

results in an ORA error:

System.Data.Entity.Core.EntityCommandExecutionException : An error occurred while executing the command definition. See the inner exception for details.
  ----> Oracle.ManagedDataAccess.Client.OracleException : ORA-12704: character set mismatch

I could ofcource remove the condition, iterate over the result and replace the null values, but I would prefer a cleaner solution. How can I avoid this error?

try `ProjectName = item == null ? "deleted project" : item.Name` instead — Zein Makki, Jun 20 '16 at 08:39
**[This](http://stackoverflow.com/q/33693374/3639582)** might help. — Shaunak D, Jun 20 '16 at 08:49

score 3 · Accepted Answer · answered Jun 20 '16 at 09:30

The problem appeared to be that the specific column was NVARCHAR.

The generated query would read:

...,
CASE WHEN ("Extent2"."NAME" IS NULL) THEN 'deleted project' ELSE "Extent2"."NAME" END AS "C1",
...

However, to make it work, the N-prefix should be used for the string constant. To get this prefix in I had to replace the line:

ProjectName = item.Name ?? "deleted project",

By:

ProjectName = item.Name ?? EntityFunctions.AsUnicode("deleted project"),

So now, the generated SQL reads:

...,
CASE WHEN ("Extent2"."NAME" IS NULL) THEN N'deleted project' ELSE "Extent2"."NAME" END AS "C1",
...

And it works.

Very good solution. `DbFunctions.AsUnicode("deleted project")` worked for me — Rafael P. Miranda, Jan 24 '19 at 17:48

C# Left join linq results in ORA-12704 when selecting a default string value

1 Answers1