I got a model and a Sql View (from another DB with another Collation setting).
I put a navigation property on my model and i want to include data from the view but it causes an error.
View defined and mapped to my class in ApplicationDbContext
builder.Entity<VariantView>().
ToView("VariantsView")
.HasKey(v => v.ItemNo);
On my EF core entity
[ForeignKey("ItemNo")]
public VariantView? VariantView { get; set;}
In my query i do like
var q = dbContext.Masters.
.Include(variant => variant!.VariantView)
.Where(m => m.Id == request.Id);
in pure sql it is easy to fix by forcing the collation on the
..
FROM Masters m
LEFT JOIN VariantsView AS vv
ON vv.ItemNo COLLATE Latin1_General_CI_AS = m.ItemNo COLLATE Latin1_General_CI_AS
..
Is there no way to add this to the EF Include method ?
Note: I got 6 others navigation props that gets included as well so rather not go all in on SQL
My solution is to add collate statement in the SQL view
So starting with
select
item.[No_] COLLATE Latin1_General_CI_AS as ItemNo,
....