Use Views in Entity Framework

Question

I am using Entity Framework on a project, but am finding the large queries, especially those which use LEFT joins, to be very tedious to write, and hard to debug.

Is it common, or accepted practice, to make use of Views in the database, and then use those views within the EntityFramework? Or is this a bad practice?

+1 I was struggling with this yesterday too. And I'd like to know how to create a view using code first... — Colin, Jul 05 '13 at 08:37
How to do left joins without the join syntax: http://blogs.teamb.com/craigstuntz/2010/01/13/38525/ — Colin, Jul 05 '13 at 12:27
Some people with legacy databases are trying to move away from views: http://stackoverflow.com/q/9016079/150342 — Colin, Jul 09 '13 at 14:34

score 1 · Answer 1 · answered Oct 15 '19 at 12:07

the question is not very clear but there is no absolute right or wrong in Software. it all depends on your case.

there is native support for views in ef core but there is no native support for views in EF < 6. at least not in the current latest version 6.3. there is, however, a work around to this. in database first you would create your view via sql normally and when you reverse engineer your database, EF will treat your view as a normal model and will allow you to consume it regularly as you would do in a normal table scenario. in Code First it's a bit more tedious. you would create a POCO object that maps to the columns in your view. notice that you need to include an Id in this POCO class. for example

public class ViewPOCO
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public Guid Id {get;set;}
    public string ViewColumn1 {get;set;}
    ... etc.
}

you would add this POCO class in your DbContext

public class MyDbContext : DbContext
{
  public virtual DbSet<ViewPOCO> MyView {get;set;}
}

now you will normally apply the command of adding migration through the package manager console

 Add-Migration <MigrationName> <ConnectionString and provider Name>

now in the migration up and down you will notice that EF treats your Model as table. you would clear all of this and write your own sql to add/alter the view in the up and drop the view in the down method using the Sql function.

 public override void Up()
 {
    Sql("CREATE OR ALTER VIEW <ViewName> AS SELECT NEWID() AS Id, ...");
 }
 public override void Down()
 {
   Sql("DROP VIEW <ViewName>");
 }

score -1 · Answer 2 · answered Jul 04 '13 at 09:33

-1

First create your view.
Update Your .edmx File.
then use like this.

using (ManishTempEntities obj = new ManishTempEntities())
{
     var a = obj.View_1.ToList();
}

answered Jul 04 '13 at 09:33

Manish Sharma

2,406
2
16
31

I'm asking more if it's more 'ok' to use views in entity framework. I want to know if it's acceptable to do the more complex left joins with views, instead of using linq. – Craig Jul 04 '13 at 09:48
1

It's more ok to have a view than to join all your data via LINQ, cause the views are precompiled in SQL(like SP) so that makes them to return an result faster. The view doesn't need to convert/cast all your data type from SQL to C# data type so that's make them faster... so if you have more and more data you will wish to use views instead classic EF(even I'm a EF fan myself). Happy coding(even this comments comes a bit to late) – HellBaby Dec 08 '14 at 18:12

Use Views in Entity Framework

2 Answers2

Linked