EF Core supports the views, here is the details.
This feature was added in EF Core 2.1 under the name of query types. In EF Core 3.0 the concept was renamed to keyless entity types. The [Keyless] Data Annotation became available in EFCore 5.0.
It is working not that much different than normal entities; but has some special points. According documentation:
- Cannot have a key defined.
- Are never tracked for changes in the DbContext and therefore are never inserted, updated or deleted on the database.
- Are never discovered by convention.
- Only support a subset of navigation mapping capabilities, specifically:
- They may never act as the principal end of a relationship.
- They may not have navigations to owned entities
- They can only contain reference navigation properties pointing to regular entities.
- Entities cannot contain navigation properties to keyless entity types.
- Need to be configured with a [Keyless] data annotation or a .HasNoKey() method call.
- May be mapped to a defining query. A defining query is a query declared in the model that acts as a data source for a keyless entity type
It is working like below:
public class Blog
{
public int BlogId { get; set; }
public string Name { get; set; }
public string Url { get; set; }
public ICollection<Post> Posts { get; set; }
}
public class Post
{
public int PostId { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public int BlogId { get; set; }
}
If you don't have an existing View at database you should create like below:
db.Database.ExecuteSqlRaw(
@"CREATE VIEW View_BlogPostCounts AS
SELECT b.Name, Count(p.PostId) as PostCount
FROM Blogs b
JOIN Posts p on p.BlogId = b.BlogId
GROUP BY b.Name");
And than you should have a class to hold the result from the database view:
public class BlogPostsCount
{
public string BlogName { get; set; }
public int PostCount { get; set; }
}
And than configure the keyless entity type in OnModelCreating using the HasNoKey:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder
.Entity<BlogPostsCount>(eb =>
{
eb.HasNoKey();
eb.ToView("View_BlogPostCounts");
eb.Property(v => v.BlogName).HasColumnName("Name");
});
}
And just configure the DbContext to include the DbSet:
public DbSet<BlogPostsCount> BlogPostCounts { get; set; }