I have a database built using model first and am now replacing the C# code to a code first approach.
.NET string properties are mapped to NVARCHAR2 (usually) or CHAR for fixed-length non-Unicode strings; but for key properties both Ends are always of the same Format.
Nevertheless I came across one Problem I could not solve yet.
Let's look at the following C# code to load data from the DbContext:
Code: Select all
await context.Plants.Where(p => p.plid == plant.plid)
.Include(p => p.CMWorkDays)
.Include(p => p.CriterionGroups)
.LoadAsync();
Code: Select all
SELECT
"UnionAll1".C2 AS C1,
"UnionAll1".PLNT_ID AS C2,
"UnionAll1".SHORTDESC AS C3,
"UnionAll1".PLANTNUMBER AS C4,
"UnionAll1".C1 AS C5,
"UnionAll1".CMWD_ID AS C6,
"UnionAll1".MONTH AS C7,
"UnionAll1".YEAR AS C8,
"UnionAll1".WORKDAYS AS C9,
"UnionAll1".PLNT_ID1 AS C10,
"UnionAll1".C3 AS C11,
"UnionAll1".C4 AS C12,
"UnionAll1".C5 AS C13,
"UnionAll1".C6 AS C14
FROM (SELECT
CASE WHEN "Extent2".YEAR IS NULL THEN TO_NUMBER(NULL) ELSE 1 END AS C1,
1 AS C2,
"Extent1".PLNT_ID,
"Extent1".SHORTDESC,
"Extent1".PLANTNUMBER,
"Extent2".CMWD_ID,
"Extent2".MONTH,
"Extent2".YEAR,
"Extent2".WORKDAYS,
"Extent2".PLNT_ID AS PLNT_ID1,
TO_NUMBER(NULL) AS C3,
TO_NUMBER(NULL) AS C4,
TO_CHAR(NULL) AS C5,
TO_CHAR(NULL) AS C6
FROM PLANTS "Extent1"
LEFT OUTER JOIN CMWORKDAYS "Extent2" ON "Extent1".PLNT_ID = "Extent2".PLNT_ID
WHERE "Extent1".PLNT_ID = :p__linq__0
UNION ALL
SELECT
2 AS C1,
2 AS C2,
"Extent3".PLNT_ID,
"Extent3".SHORTDESC,
"Extent3".PLANTNUMBER,
TO_NUMBER(NULL) AS C3,
TO_NUMBER(NULL) AS C4,
TO_NUMBER(NULL) AS C5,
TO_NUMBER(NULL) AS C6,
TO_NUMBER(NULL) AS C7,
"Extent4".CRGR_ID,
"Extent4".PLNT_ID AS PLNT_ID1,
"Extent4".SHORTDESC AS SHORTDESC1,
"Extent4".LONGDESC
FROM PLANTS "Extent3"
INNER JOIN CRITERIONGROUPS "Extent4" ON "Extent3".PLNT_ID = "Extent4".PLNT_ID
WHERE "Extent3".PLNT_ID = :p__linq__0) "UnionAll1"
ORDER BY "UnionAll1".PLNT_ID ASC, "UnionAll1".C1 ASC
I tried different ways to solve that already.
First shot:
Using the Devart Attribute Devart.Data.Linq.Mapping.Column which is from the underlying data Provider allows to specify the database type, this didn't work.
Second shot:
Using the OnModelCreating() function overwrite I tried it with
Code: Select all
modelBuilder.Properties()
.Where(p => p.PropertyType == typeof(string))
.Configure(p => p.HasColumnType("NVARCHAR2"));
NVARCHAR2 leads to the nulls being converted with TO_NCHAR (which is still the wrong type).
NCLOB leads to the nulls being converted by TO_NCLOB (wrong again),
CHAR leads to conversion using TO_CHAR(),
But how to get it converted to NVARCHAR2 or at least any type that would work?
regards and thanks
jongleur