Highway.Data

Highway.Data and Advanced Query Scanarios

*This is written for version 3.5 and later*

{ public class Person { public int Id { get; set; } public string FirstName { get; set; } public string LastName { get; set; } } }</pre>

namespace Highway.Data.GettingStarted.Domain.Entities
{
    public class Account
    {
        public int AccountId { get; set; }

        public string AccountName { get; set; }
    }
}

using System.Data.Entity;
using Highway.Data.GettingStarted.Domain.Entities;

namespace Highway.Data.GettingStarted.DataAccess.Mappings
{
    public class GettingStartedMappings : IMappingConfiguration
    {
        public void ConfigureModelBuilder(DbModelBuilder modelBuilder)
        {
            //Approach 1
            modelBuilder.Entity<Person>().HasKey(x=>x.Id).ToTable("People");

            //Approach 2
            modelBuilder.Configurations.Add(new AccountMap());
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Highway.Data.GettingStarted.DataAccess.Mappings;

namespace Highway.Data.GettingStarted.AdvancedQueries
{
    class Program
    {
        static void Main(string[] args)
        {
            var context = new DataContext("Data Source=(local);Initial Catalog=GettingStarted;Integrated Security=true;",
                           new GettingStartedMappings());
            var repository = new Repository(context);
        }
    }
}

using System.Data.Entity.ModelConfiguration;
using Highway.Data.GettingStarted.Domain.Entities;

namespace Highway.Data.GettingStarted.DataAccess.Mappings
{
    public class AccountMap : EntityTypeConfiguration<Account>
    {
        public AccountMap()
        {
            this.ToTable("Accounts");
            this.HasKey(x => x.AccountId);
            this.Property(x => x.AccountName).HasColumnType("text");
        }
    }
}

Ok, now that we have our domain objects, their mappings, and the repository and context built we need to execute some embedded SQL *I Know….* against Entity Framework but within a query object for consistency. We need to write the Query first.

using System.Linq;
using Highway.Data.GettingStarted.Domain.Entities;
using Highway.Data.QueryObjects;

namespace Highway.Data.GettingStarted.AdvancedQueries
{
    public class FindPersonByLastNameEmbeddedSQLQuery : AdvancedQuery<Person>
    {
        public FindPersonByLastNameEmbeddedSQLQuery(string lastName)
        {
            ContextQuery = delegate(DataContext context)
                {
                    var sql = "Select * from People p where p.LastName = @last";
                    var results = context.Database.SqlQuery<Person>(sql, lastName);
                    return results.AsQueryable();
                };
        }
    }
}

Notice it inherits from AdvancedQuery instead of Query. This is to make sure you know you are using coupling behavior that will not be automatically portable to another data access implementation.

Secondly I use a delegate definition there to make sure you can see that you get the DataContext which is a DbContext. This gives you everything Entity Framework has to offer there. You just get to use it, the same way you use other queries.

IEnumerable<Person> results = 
     repository.Find(new FindPersonByLastNameEmbeddedSQLQuery("Liles"));

It is that simple. *For NHibernate you get an ISession, and for Raven you get an IDocumentSession*

“Hop on the highway and see where it takes you”

Go Back to Getting Started with IoC