Entity Framework Core Use Include on QueryType(Database View)

Question

I have EF Core connected to MySql and I have a View called:

PostViews

I read this article saying I can use Query Types for Database Views.

It works if I just call _context.PostViews, but if I use Include on it like:

_context.PostViews.Include(xxxx), it throws me this error:

System.InvalidOperationException: 'The property 'Comment' is not a navigation property of entity type 'PostWithViews'. The 'Include(string)' method can only be used with a '.' separated list of navigation property names.'

PostView has all properties in Post (id, title, content, Comment etc.), plus it has one extra column called: Views which shows how many people have read this post.

Here is my Post:

public partial class Post
    {
        public Post()
        {
            Comment = new HashSet<Comment>();
        }

        public string Id { get; set; }
        public string ApartmentId { get; set; }
        public string AuthorId { get; set; }
        public string CategoryId { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }
        public DateTime? DueDate { get; set; }
        public bool? Disabled { get; set; }
        public DateTime? CreatedAt { get; set; }
        public DateTime? UpdatedAt { get; set; }
        public string UpdatedBy { get; set; }

        public virtual Apartment Apartment { get; set; }
        public virtual User Author { get; set; }
        public virtual PostCategory Category { get; set; }}
        public virtual ICollection<Comment> Comment { get; set; }
    }

PostWithViews class is almost the same with Post but with 1 more property Views:

public int Views { get; set; }

Here is how I include properties e.g. Comment :

return GetAll()
       .Include(p => p.Author)
       .Include(p => p.Comment)

Exception message is telling you the problem. What is the type of the Comment property? — Ivan Stoev
– Ivan Stoev, Commented May 15, 2019 at 8:19
Hi, take a look at Compare query types to entity types. You've probably missed the following bullet "They can only contain reference navigation properties pointing to entities.". So there is no way to make Comment included since it is a collection navigation property. But including Apartment, Author and Category should work. — Ivan Stoev
– Ivan Stoev, Commented May 15, 2019 at 16:12
hi @IvanStoev thanks for pointing out that point. could you please make it an answer, I'd like to mark it as the answer. thanks — Franva
– Franva, Commented May 15, 2019 at 17:59

Ivan Stoev · Accepted Answer · 2019-05-15 19:27:56Z

3

Currently (EF Core 2.x) query types do not support collection navigation properties, as mentioned in the Compare query types to entity types documentation topic:

They can only contain reference navigation properties pointing to entities.

So although your Comment property looks like collection navigation property, for EF Core it s isn't, hence cannot be used in Include / ThenInclude (or LINQ to Entities query).

However Apartment, Author and Category are "reference navigation properties pointing to entities", so they should be fully functional.

answered May 15, 2019 at 19:27

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

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

6 Comments

Simon_Weaver Over a year ago

So this is true for EF Core 3 also right? I am trying to 'fake' a naviation property by doing

var people = dbContext.People.Select(p => new { PersonName = p.Name, Cats = dbContext.AnimalsView.Where(a => a.Species == "cat" && a.OwnerID == p.ID }).ToArray() })

. But I only ever get one cat per person!

Ivan Stoev Over a year ago

@Simon_Weaver (1) Looks like so (2) Quite surprising, definitely a bug.

Simon_Weaver Over a year ago

‘Animals’ is actually a view that has no key set (therefore can’t just use navigation props). If I run it ‘outside’ in a loop I get all the expected cats in the c# array and they’re all in the join in the executed sql.

Ivan Stoev Over a year ago

@Simon_Weaver Indeed, I meant you found yet another EF Core bug. Actually I'm getting not only one, but also one and the same cat per each person!

Ivan Stoev Over a year ago

First (probably). Who cares, bug is a bug.

|

Luiz · Accepted Answer · 2019-05-14 19:42:02Z

0

an include function needs a query parameter but it still does not know its object so you need to use a lambda function to pass its object and the parameter you want

try it: _context.PostView.Include(lambda => lambda.Comment);

answered May 14, 2019 at 19:42

Luiz

211 silver badge5 bronze badges

Collectives™ on Stack Overflow

Entity Framework Core Use Include on QueryType(Database View)

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related