5

Lets assume I have and entity called Debt:

public class Debt
{
    [Key]
    public int Id { get; set; }
    public int Amount { get; set; }
    public int UserId { get; set; }
}

I'm using Code first, so I just simply introduce IDbSet<Debt>and use it.

After that I want to add some security for read on DB level: I've created a view called Debt_Read:

CREATE VIEW Debt_Read AS SELECT * FROM Debt WHERE UserId IN (1,2,3)

Let's keep view body simple, in real life this code use some sql function for retrieving user id from session.

I wan't EF to map my DbSet<Debt> to read from VIEW and to write update and create to TABLE.

How can I achieve this?

Alexey Rebrov
  • 51
  • 2

1 Answers1

0

You can use Dapper.
Dapper is a micro-ORM created by Stack Overflow.
You can use EF to CUD and Dapper to Read.

With the Dapper you can build your query.
In this case, can you build your select query using the view (Debt_Read).

A quick example:

public Debt FindAll()    
{
   var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["BD"].ConnectionString);

   using (IDbConnection db = conn)
   {
      return db.Query<Debt>("Select * From Debt_Read");
   }
}

If you're interested read this article where Julia Lerman writes about applications that use ef and dapper.

https://msdn.microsoft.com/en-us/magazine/mt703432

Hope it's useful!

Lucas Limão
  • 105
  • 5