Create DbQuery out of View table

c# ef-core-2.1 entity-framework-core sql-view

Question

I am trying to create a DbQuery of ExtendedStudent, from SQL View which was constructed form 2 diffrent tables (see code SQL below).

I have looked at the following posts:

Entity Framework Core Query Types And EF Core 2.1 Query Types Both have used a model with a navigation propery in it, and then succeeded to fetch it from the Fluent Fluent API. But When i tried to do so too, i got exception such as "Invalid column name 'PrefixId1'

The models I use are:

public class ExtendedStudent {

    public int IdNumber {get; set;}

    public string FirstName {get; set;}

    public string LastName {get; set;}

    public virtual Prefix Prefix {get; set;}

    public int Score {get; set;}
}

public class Prefix {

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string Id {get ;set;}

    [required]
    public string Name {get; set;}
}

The applicationDbContext.cs file is:

public class ApplciationDbContext : DbContext{

    DbSet<Prefix> Prefixes {get; set;}

    DbQuery<ExtendedStudent> ExtendedStudents {get ;set;}

    ...

    protected override void OnModelCreating(ModelBuilder builder) {

        builder.Query<ExtendedStudent>.ToView("ExtendedStudent");
        builder.Query<ExtendedStudent>.HasOne<Prefix>().WithMany();
    }
}

At last, I tried to fetch the data like this.

var students = applciationDbContext.ExtendedStudents.Include(v => v.Prefix).ToList();

I have created the ExtendedStudents view in SQL like this:


CREATE VIEW [Organization].[ExtendedStudent] AS
SELECT [TableA].[Student].[FirstName]
        ,[TableA].[Student].[LastName]
        ,[TableA].[Student].[PrefixId]
        ,[TableA].[Student].[IdNumber]
        ,[Evaluation].[Student].[Score]
FROM [TableA].[Student] AS [Students]
INNER JOIN [Evaluation].[Student] ON [Evaluation].[Student].StudentId = [TableA].[Student].[IdNumber]

I have tried to add a PrefixId property to ExtendedStudent, or add foreign key, But nothing have worked.

I got an error saying

"An exception of type 'System.Data.SqlClient.SqlException' occured in Microsoft.EntityFrameworkCore.dll but was not handled in user code: 'Invalid column name 'PrefixId1'.'

1
1
8/8/2019 11:39:08 AM

Accepted Answer

Here

builder.Query<ExtendedStudent>.HasOne<Prefix>().WithMany();

with .HasOne<Prefix>() you are telling EF Core to create many to one relationship without navigation property at each end.

But the navigation property ExtendedStudent.Prefix already implies relationship, hence EF Core assumes a second relationship with default FK property and column name PrefixId1 (because PrefixId is already used by the "other" relationship implied from the navigation property).

To fix that, pass the navigation property to the relationship configuration:

builder.Query<ExtendedStudent>.HasOne(e => e.Prefix).WithMany();
1
8/8/2019 12:01:55 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow