Create DbQuery out of View table

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'.'

Ivan Stoev · Accepted Answer · 2019-08-08 12:01:55Z

1

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();

edited Aug 8, 2019 at 12:01

answered Aug 8, 2019 at 11:56

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ido Over a year ago

Thanks, that seemed to work, But in my case I use lazyloadingProxies, so I added the virtual keyword to the navigation property, But I recieved an error saying "getting value from 'Prefix' on 'Castle.Proxies.ExtendedStudentProxy --> Unable to track an instance of type 'ExtendedStudent' because it is a query type, Only entity types may be tracked. Do you know why it is tracked?

Ivan Stoev Over a year ago

I guess you should remove the virtual from this property of ExtendedStudent, because ExtendedStudent is query type and should not be proxied. At least this is how I read the error message.

Ivan Stoev Over a year ago

Anyway, that's a different issue, you can post it as separate question if you wish.

Collectives™ on Stack Overflow

Create DbQuery out of View table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related