Views in an RDBMS fulfill an important role. They allow developers to combine data from multiple tables into a structure that looks like a table, but do not provide persistence. Thus, we have an abstraction on top of raw table data. One can use this approach to provide different security rights, for example. We can also simplify queries we have to write, especially if we access the data defined by views quite frequently in multiple places in our code. Entity Framework Code-First does not fully support views as of now. As a result, we have to use a workaround. One approach would be to write code as if a view was really a table, that is, let Entity Framework define this table, then drop the table, and create a replacement view. We will still end up with strongly typed data with full query support. Let's start with the same database structure we used before, including person and person type. Our view will combine a few columns from the Person table and Person type name, as shown in the following code snippet:

public class PersonViewInfo
{
    public int PersonId { get; set; }
    public string TypeName { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Here is the same class in VB.NET:

Public Class PersonViewInfo
    Public Property PersonId() As Integer
    Public Property TypeName() As String
    Public Property FirstName() As String
    Public Property LastName() As String
End Class

Now, we need to create a configuration class for two reasons. We need to specify a primary key column because we do not follow the naming convention that Entity Framework assumes for primary keys. Then, we need to specify the table name, which will be our view name, as shown in the following code:

public class PersonViewInfoMap :
    EntityTypeConfiguration<PersonViewInfo>
{
    public PersonViewInfoMap()
    {
        HasKey(p => p.PersonId);
        ToTable("PersonView");
    }
}

Here is the same class in VB.NET:

Public Class PersonViewInfoMap
    Inherits EntityTypeConfiguration(Of PersonViewInfo)
    Public Sub New()
        HasKey(Function(p) p.PersonId)
        ToTable("PersonView")
    End Sub
End Class

Finally, we need to add a property to our context that exposes this data, as shown here:

public DbSet<PersonViewInfo> PersonView { get; set; }

The same property in VB.NET looks quite familiar to us, as shown in the following code:

Property PersonView() As DbSet(Of PersonViewInfo)

Now, we need to work with our initializer to drop the table and create a view in its place. We are using one of the initializers we created before. When we cover migrations, we will see that the same approach works there as well, with virtually identical code. Here is the code we added to the Seed method of our initializer, as shown in the following code:

public class Initializer : DropCreateDatabaseIfModelChanges<Context>
{
    protected override void Seed(Context context)
    {
        context.Database.ExecuteSqlCommand("DROP TABLE PersonView");
        context.Database.ExecuteSqlCommand(
            @"CREATE VIEW [dbo].[PersonView]
            AS
            SELECT
              dbo.People.PersonId,
              dbo.People.FirstName,
              dbo.People.LastName,
              dbo.PersonTypes.TypeName
            FROM    
              dbo.People
            INNER JOIN dbo.PersonTypes
              ON dbo.People.PersonTypeId = dbo.PersonTypes.PersonTypeId
            ");
    }
}

In the preceding code, we first drop the table using the ExecuteSqlCommand method of the Database object. This method is useful because it allows the developer to execute arbitrary SQL code against the backend. We call this method twice, the first time to drop the tables and the second time to create our view.

The same initializer code in VB.NET looks as follows:

Public Class Initializer
    Inherits DropCreateDatabaseIfModelChanges(Of Context)
    Protected Overrides Sub Seed(ByVal context As Context)
        context.Database.ExecuteSqlCommand("DROP TABLE PersonView")
        context.Database.ExecuteSqlCommand( <![CDATA[
            CREATE VIEW [dbo].[PersonView]
            AS
            SELECT
              dbo.People.PersonId,
              dbo.People.FirstName,
              dbo.People.LastName,
              dbo.PersonTypes.TypeName
            FROM    
              dbo.People
            INNER JOIN dbo.PersonTypes
              ON dbo.People.PersonTypeId = dbo.PersonTypes.PersonTypeId]]>.Value())
    End Sub
End Class

Since VB.NET does not support multiline strings such as C#, we are using XML literals instead, getting a value of a single node. This just makes SQL code more readable.

We are now ready to query our data. This is shown in the following code snippet:

using (var context = new Context())
{
    var people = context.PersonView
        .Where(p => p.PersonId > 0)
        .OrderBy(p => p.LastName)
        .ToList();
    foreach (var personViewInfo in people)
    {
        Console.WriteLine(personViewInfo.LastName);
    }

As we can see, there is literally no difference in accessing our view or any other table. Here is the same code in VB.NET:

Using context = New Context()
    Dim people = context.PersonView _
            .Where(Function(p) p.PersonId > 0) _
            .OrderBy(Function(p) p.LastName) _
            .ToList()
    For Each personViewInfo In people
        Console.WriteLine(personViewInfo.LastName)
    Next
End Using

If we do not want to play around with tables in such a way, we can still use the initializer to define our view, but query the data using a different method of the Database object, SqlQuery. This method has the same parameters as ExecuteSqlCommand, but is expected to return a result set, in our case, a collection of PersonViewInfo objects, as shown in the following code:

using (var context = new Context())
{
    var sql = @"SELECT * FROM PERSONVIEW WHERE PERSONID > {0} ";
    var peopleViaCommand = context.Database.SqlQuery<PersonViewInfo>(
        sql,
        0);
    foreach (var personViewInfo in peopleViaCommand)
    {
        Console.WriteLine(personViewInfo.LastName);
    }
}

The SqlQuery method takes generic type parameters, which define what data will be materialized when a raw SQL command is executed. The text of the command itself is simply parameterized SQL. We need to use parameters to ensure that our dynamic code is not subject to SQL injection. SQL injection is a process in which a malicious user can execute arbitrary SQL code by providing specific input values. Entity Framework is not subject to such attacks on its own. Here is the same code in VB.NET:

Using context = New Context()
    Dim sql = "SELECT * FROM PERSONVIEW WHERE PERSONID > {0} "
    Dim peopleViaCommand = context.Database.SqlQuery(Of PersonViewInfo)(sql, 0)
        For Each personViewInfo In peopleViaCommand
        Console.WriteLine(personViewInfo.LastName)
    Next
End Using

We not only saw how to use views in Entity Framework, but saw two extremely useful methods of the Database object, which allows us to execute arbitrary SQL statements and optionally materialize the results of such queries. The generic type parameter does not have to be a class. You can use the native .NET type, such as a string or an integer.