Archive for the ‘Entity Framework’ Category.

SQL Query to Entity Framework Cheat Sheet

The following are examples of SQL queries and how to execute them with Entity Framework

SELECT * with Entity Framework

SELECT * FROM Contacts
var mydbContext.Contacts.ToList();

SELECT Id with Entity Framework

This is really for how to select any single column.

SELECT Id FROM Contacts
List<int> idList = dbContext.Contacts.Select( c => c.Id ).ToList(); // Assuming Id is of type int

SELECT only one row with Entity Framework

This is really for how to select any single row from a table.

SELECT * FROM Contacts where Id = 10
var id = 10;
var contact = dbContext.Contacts.SingleOrDefault( c => c.Id = id );

SELECT only one result with Entity Framework

This is really for how to select any single value from a single row in a table.

SELECT UserId FROM Contacts where Id = 10
var id = 10;
int userId = dbContext.Contacts.Where( c => c.Id = id ).Select( c => c.UserId ).SingleOrDefault();

SELECT first result with Entity Framework

This is really for how to select the first value from a list of returned rows from a table.

SELECT TOP 1 * FROM Contacts
var id = 10;
int userId = dbContext.Contacts.FirstOrDefault();

INSERT INTO with Entity Framework

INSERT INTO dbo.LD_User (Name, Type, Active, CreateDate, CreatedBy)
VALUES ('user1', 1, 1, GetDate(), 101)
var user = new User { Name = "user1", Type = UserType.Contact, true, DateTime.Now, User.System };
dbContext.Users.Add(user);
dbContext.SaveChanges();

Note: UserType.Contact and User.System are enums in the above example.

INSERT INTO … SELECT with Entity Framework

Imagine you have these three tables. Product, ProductGroup, ProductGroupMembership. You want to make Products a member of a group by inserting into the ProductGroupMembership table.

INSERT INTO ProductGroupMembership (ProductId, GroupId, CreateDate, CreatedBy)
SELECT Id, @GroupId, GetDate(), @CreatedBy FROM Product
WHERE Id IN (1, 2, 3, 4, 5) -- there could be hundreds or thousands of numbers in the IN statement
EPIC FAIL!!! Can't be done without raw sql and opening up to sql injection attacks.

However, you can create a stored procedure that takes and user-defined table called ArrayOfInt. Then add EntityFrameworkExtras.EF6, which is available as a NuGet package, to call the storedprocedure and pass it an array.

WHERE with many AND/ORs with Entity Framework

Imagine you have these three tables. Product, ProductGroup, ProductGroupMembership. You want to make Products a member of a group by inserting into the ProductGroupMembership table.

You have a list of software Products provided to you. However, the input only includes Product.Name and Product.Version. You now need to check if the products exist and get the id.

SELECT Id FROM Product
WHERE (Name = 'Product 1' AND Version = '10.0')
WHERE (Name = 'Product 2' AND Version = '10.0')
WHERE (Name = 'Product 3' AND Version = '10.1')
WHERE (Name = 'Product 4' AND Version = '10.0')
WHERE (Name = 'Product 5' AND Version = '1.0')
EPIC FAIL!!! Can't be done without raw sql

However, you can add LinqKit’s PredicateBuilder to do this. PredicateBuilder works on top of Entity Framework and is available as a NuGet package. See how I used it here: Entity Framework and many WHERE clauses

Entity Framework and many WHERE clauses

So today, I needed to get Entity Framework to return me a list of Products from the database based on a list of Product.Name and Product.Version values (not Ids). If it were Product.Id, it would have been simple as I could have used an IN statement, but it wasn’t.

The query might get many (maybe hundreds at a time) products based on the list. Here is the query I imagined.

So when doing a query like this, since there could be hundreds, I have a couple of options.

  1. Query the database once for each product.
    SELECT * FROM dbo.Product
    WHERE (Name = 'Product 1' AND Version = '10.0')
    

    Repeat this same query once for each Product.

  2. Query the database one time with an or clause for each Product.Name and Product.Version.
    SELECT * FROM dbo.Product
    WHERE (Name = 'Product 1' AND Version = '10.0')
       OR (Name = 'Product 2' AND Version = '10.0')
       OR (Name = 'Product 3' AND Version = '10.0')
       OR (Name = 'Product 4' AND Version = '1.0')
       -- There could be hundreds
    
  3. Query the database once and get all products and use code to find the ones I wanted.
    SELECT * FROM dbo.Product
    

Option 1
I didn’t like this option because I could end up doing hundreds of single queries. That doesn’t sound like a good idea. What would the performance impact would be when doing hundreds of single queries? The overhead of traversing over the network to the database would prevent this option from scaling.

Option 2
This is the option I imagined in my head. My gut said to use this option.

Option 3
This would work. We only have about two thousand products today and querying them all would, right now, not be bad at all. However, we just bought a company and will be adding more products. We plan to buy more companies. Also, we have two companies that we have already bought and have yet to add those products in. When would the number of Product rows in the database make the SELECT * and invalid option? Doing this would work now, but it leave a time bomb for some future developer encounter and have to fix.

Winner: Option 2

Problem
Entity Framework doesn’t really have an easy way to create the Option 2 query.

So, how do I create this query with many where statements?

Here are the two options I’ve found:

Inline SQL Query with Entity Framework

        private static List<Product> GetProductsByNameAndVersion(ActivationDbContext dbContext, IEnumerable<ProductRequest> products)
        {
            if (!products.Any())
            {
                return new List<Product>();
            }
            var query = "Select * FROM Product WHERE ";
            var or = "";
            var template = "(Name = '{0}' AND Version = '{1}')";
            foreach (var prod in products)
            {
                query += or;
                query += string.Format(template, prod.Name, prod.Version);
                or = " OR ";
            }
            var dbProducts = dbContext.Products.SqlQuery(query).ToList();
            return dbProducts.ToList();
        }

This option means I have to create magic strings and make sure that I handle the strings correctly. It has bugs already. Such as what if a product only has a name and not a version (version could be null or empty, who knows) or vice-versa? How would this affect my query string?

PredicateBuilder

Predicate Builder from the LinqKit library which is available as a NuGet package.

        private static List<Product> GetProductsByNameAndVersionPredicate(ActivationDbContext dbContext, IEnumerable<ProductRequest> products)
        {
            if (!products.Any())
            {
                return new List<Product>();
            }

            var predicate = PredicateBuilder.False<Product>();

            foreach (var prod in products)
            {
                var inner = PredicateBuilder.True<Product>();
                inner = inner.And(p => p.Name== prod.Name);
                inner = inner.And(p => p.Version == prod.Version);
                predicate = predicate.Or(inner);
            }
            var dbProducts = dbContext.Products.AsExpandable().Where(predicate).ToList();
            return dbProducts;            
        }

PredicateBuilder isn’t very intuitive. For starters, what is the different between these methods:

  • PredicateBuilder.True() – from what I understand this would be more appropriate and understandable as PredicateBuilder.And()
  • PredicateBuilder.False() – from what I understand this would be more appropriate and understandable as PredicateBuilder.Or()

Also, you have to remember to call AsExpandable() on the first call to a table in order to use it.

Conclusion

I am going to go with PredicateBuilder for now. It feels cleaner than rolling my own string query. But both solutions ultimately worked. That means that Entity Framework ultimately provided me a solution without an extra library. However, LinqKit saved me from magic strings. My only question is this: Why isn’t a predicate builder built into Entity Framework?

AutoMapper versus Extension Methods versus Implicit Casts

Download Project
Imagine the database code is legacy, can’t be changed, and the Database Person object from the database namespace looks like this:

using System;
using System.ComponentModel.DataAnnotations;

namespace WcfToEntityAutomapperExample.DAL.Model
{
    /// <summary>
    /// Example of an Person object where properties are not defined in a way you want 
    /// to expose via WCF.
    /// </summary>
    class PersonRow
    {
        public int Id { get; set; }

        [Required]
        [StringLength(50)]
        public string FName { get; set; }

        [Required]
        [StringLength(50)]
        public string MName { get; set; }

        [Required]
        [StringLength(50)]
        public string LName { get; set; }

        [Required]
        public DateTime BD { get; set; }

        public DateTime? DD { get; set; }
    }
}

You can’t use this in your WCF service, and not just beccause PersonRow, FName and LName just look tacky, though that is reason alone. No, the real problem is ambiguity and confusion. MName isn’t exactly clear. Is it Maiden name, Mother’s last name, or middle name? And what is BD and DD? Such confusion and ambiguity isn’t acceptable in an exposed API.

So you create a Person DataContract to expose with WCF that looks like this:

using System;
using System.Runtime.Serialization;

namespace WcfToEntityAutomapperExample.Services.Model
{
    [DataContract]
    public class Person
    {
        [DataMember]
        public string FirstName { get; set; }
        [DataMember]
        public string MiddleName { get; set; }
        [DataMember]
        public string LastName { get; set; }
        [DataMember]
        public DateTime DateOfBirth { get; set; }
        [DataMember]
        public DateTime? DateOfDeath { get; set; }
    }
}

Wow. That will look much better in your exposed WCF API.

So now there is a problem. We need to convert Person to PersonRow.

Solving with extension methods

It would be easy to write extension methods to do this:

  1. Add an extension method
    using WcfToEntityAutomapperExample.DAL.Model;
    using WcfToEntityAutomapperExample.Services.Model;
    
    namespace WcfToEntityAutomapperExample.Extensions
    {
        static class PersonExtensions
        {
            public static PersonRow ToPersonRow(this Person person)
            {
                return new PersonRow
                {
                    FName = person.FirstName,
                    MName = person.MiddleName,
                    LName = person.LastName,
                    BD = person.DateOfBirth,
                    DD = person.DateOfDeath
                };
            }
        }
    }
    
  2. Add a reverse mapping extension method.

    OK. Now we have this extension method and we can use it anywhere we want. However, we forgot. We need to do this in reverse too. We need an extension method for PersonRow to Person. So add this method to PersonExtensions class.

    using WcfToEntityAutomapperExample.DAL.Model;
    using WcfToEntityAutomapperExample.Services.Model;
    
    namespace WcfToEntityAutomapperExample.Extensions
    {
        static class PersonExtensions
        {
            public static PersonRow ToPersonRow(this Person person)
            {
                return new PersonRow
                {
                    FName = person.FirstName,
                    MName = person.MiddleName,
                    LName = person.LastName,
                    BD = person.DateOfBirth,
                    DD = person.DateOfDeath
                };
            }
    
            public static Person ToPerson(this PersonRow personRow)
            {
                return new Person
                {
                    FirstName = personRow.FName,
                    MiddleName = personRow.MName,
                    LastName = personRow.LName,
                    DateOfBirth = personRow.BD,
                    DateOfDeath = personRow.DD
                };
            }
        }
    }
    
  3. Now, you have 40 other objects to do this too.
    Hint: Take a moment to compare this to the AutoMapper method below and ask yourself which is better.

  4. Use the extension method in the web service call.
    using System.Collections.Generic;
    using System.Linq;
    using WcfToEntityAutomapperExample.DAL;
    using WcfToEntityAutomapperExample.DAL.Model;
    using WcfToEntityAutomapperExample.Extensions;
    using WcfToEntityAutomapperExample.Services.Interfaces;
    using WcfToEntityAutomapperExample.Services.Model;
    
    namespace WcfToEntityAutomapperExample.Services
    {
         public class Service1 : IService1
        {
            public void AddPersonExtensionMethod(Person person)
            {
                using (var dbContext = new PersonDbContext())
                {
                    dbContext.People.Add(person.ToPersonRow());
                    dbContext.SaveChanges();
                }
            }
    
            public List<Person> FindExtensionMethod(string lastName)
            {
                using (var dbContext = new PersonDbContext())
                {
                    var foundPeopleFromDb = dbContext.People.Where(p => p.LName == lastName).ToList();
                    return foundPeopleFromDb.Select(p => p.ToPerson()).ToList();
                }
            }
        }
    }
    

Extension Method Conclusion

Simple. Easy to use. Easy to read. Makes sense. The extension method name is an important part of this clarity. I used ToPerson and ToPersonRow. But it would also work with AsPerson and AsPersonRow.

Anybody can read this code and understand it.

If another field is added it is easy to add to the extension method on a single place so code isn’t strewn about.

Using AutoMapper

Why is AutoMapper better than the above extension method? Let’s do the same thing with AutoMapper. You be the judge of whether it is a better solution.

Well, so far, I can’t find any benefit from AutoMapper.

Here is what I need to do:

  1. Add AutoMapper library from NuGet. That adds a dll and another dependency to maintain.
  2. Create a static class to configure AutoMapper mappings: AutoMapperConfig.cs.
  3. Add mappings both ways: From Person to PersonRow and from PersonRow to Person.
    using AutoMapper;
    using WcfToEntityAutomapperExample.DAL.Model;
    using WcfToEntityAutomapperExample.Services.Model;
    
    namespace WcfToEntityAutomapperExample.Map
    {
        public static class AutoMapperConfig
        {
            internal static void RegisterMappings()
            {
                Mapper.CreateMap<Person, PersonRow>()
                    .ForMember(dest => dest.FName, opt => opt.MapFrom(src => src.FirstName))
                    .ForMember(dest => dest.MName, opt => opt.MapFrom(src => src.MiddleName))
                    .ForMember(dest => dest.LName, opt => opt.MapFrom(src => src.LastName))
                    .ForMember(dest => dest.BD, opt => opt.MapFrom(src => src.DateOfBirth))
                    .ForMember(dest => dest.DD, opt => opt.MapFrom(src => src.DateOfDeath)).ReverseMap();
            }
        }
    }
    
    

    Now, you have 40 other objects to do this too.
    Hint: Take a moment to compare this to the extension method above and ask yourself which is better.

  4. Find a global location to call AutoMapperConfig.cs: Global.asax/Global.asax.cs.
    Note: If you don’t have a Global.asax/Global.asax.cs, then you need to add this.

    using System;
    using System.Web;
    using WcfToEntityAutomapperExample.Map;
    
    namespace WcfToEntityAutomapperExample
    {
        public class Global : HttpApplication
        {
            protected void Application_Start(object sender, EventArgs e)
            {
                AutoMapperConfig.RegisterMappings();
            }
    
            protected void Session_Start(object sender, EventArgs e)
            {
    
            }
    
            protected void Application_BeginRequest(object sender, EventArgs e)
            {
    
            }
    
            protected void Application_AuthenticateRequest(object sender, EventArgs e)
            {
    
            }
    
            protected void Application_Error(object sender, EventArgs e)
            {
    
            }
    
            protected void Session_End(object sender, EventArgs e)
            {
    
            }
    
            protected void Application_End(object sender, EventArgs e)
            {
    
            }
        }
    }
    
  5. Call it in your service.
    using System.Collections.Generic;
    using System.Linq;
    using AutoMapper;
    using WcfToEntityAutomapperExample.DAL;
    using WcfToEntityAutomapperExample.DAL.Model;
    using WcfToEntityAutomapperExample.Extensions;
    using WcfToEntityAutomapperExample.Services.Interfaces;
    using WcfToEntityAutomapperExample.Services.Model;
    
    namespace WcfToEntityAutomapperExample.Services
    {
         public class Service1 : IService1
        {
            public void AddPersonAutoMapper(Person person)
            {
                using (var dbContext = new PersonDbContext())
                {
                    dbContext.People.Add(Mapper.Map<PersonRow>(person));
                    dbContext.SaveChanges();
                }
            }
    
            public List<Person> FindAutoMapper(string lastName)
            {
                using (var dbContext = new PersonDbContext())
                {
                    var foundPeopleFromDb = dbContext.People.Where(p => p.LName == lastName).ToList();
                    return foundPeopleFromDb.Select(Mapper.Map<Person>).ToList();
                }
            }
        }
    }
    

AutoMapper conclusion
Something just isn’t right here. You have to call more complex code to get an object converted. The ReverseBack() method saves us from having to create the reverse copy manually. Still, there are two methods and three lambda’s per property to copy. Hardly saving code or making life easier.

The configuration code looks way more complex than the extension method code. I had a bug in my mapper config, and I couldn’t see it because the code is so busy.

The AutoMapper code also isn’t intuitive. The methods aren’t obvious and it is not clear what it is doing without reading the documentation. Mapper.Map(p) doesn’t not clearly tell me that I am converting from an object of type Person to an object of type PersonRow. To me a Map is a HashTable or a Dictionary. I assume at first glance, that I am calling some type of Dictionary. The syntax breaks the “code should be clear” and the “code should be self documenting” rules. Any developer not familiar with AutoMapper will have no idea what your code is doing.

Note: AutoMapper supposedly adds a feature that allows for a copy if the properties are the same with just one line of code: Mapper.CreateMap();

I can see how if you had a lot of objects with identical properties that AutoMapper would be tempting. Still, the naming and lack of readability gets to me. Mapping.Map(p) just isn’t clear. If all the properties of all the objects match, scripting a clear, self-documenting extension method pre-build would be the way to go. We need a pre-build solution, not a runtime solution.

If a field is added and named the same, nothing has to be done and AutoMapper works. However, if the fields are named differently, then you still have to add it to the Mapper config.

Implicit Casts

You could do this with Implicit casts.

  1. Add an implicit cast tot he object under your control, Person.
    using System;
    using System.Runtime.Serialization;
    using WcfToEntityAutomapperExample.DAL.Model;
    
    namespace WcfToEntityAutomapperExample.Services.Model
    {
        [DataContract]
        public class Person
        {
            [DataMember]
            public string FirstName { get; set; }
            [DataMember]
            public string MiddleName { get; set; }
            [DataMember]
            public string LastName { get; set; }
            [DataMember]
            public DateTime DateOfBirth { get; set; }
            [DataMember]
            public DateTime? DateOfDeath { get; set; }
    
            // User-defined conversion from Digit to double 
            public static implicit operator Person(PersonRow personRow)
            {
                return new Person
                {
                    FirstName = personRow.FName,
                    MiddleName = personRow.MName,
                    LastName = personRow.LName,
                    DateOfBirth = personRow.BD,
                    DateOfDeath = personRow.DD
                };
            }
            //  User-defined conversion from double to Digit 
            public static implicit operator PersonRow(Person person)
            {
                return new PersonRow
                {
                    FName = person.FirstName,
                    MName = person.MiddleName,
                    LName = person.LastName,
                    BD = person.DateOfBirth,
                    DD = person.DateOfDeath
                };
            }
        }
    }
    

    The implicit cast is not included in the client code so it is fine to add to the Person DataContract.

  2. Use it in your services.
    using System.Collections.Generic;
    using System.Linq;
    using AutoMapper;
    using WcfToEntityAutomapperExample.DAL;
    using WcfToEntityAutomapperExample.DAL.Model;
    using WcfToEntityAutomapperExample.Extensions;
    using WcfToEntityAutomapperExample.Services.Interfaces;
    using WcfToEntityAutomapperExample.Services.Model;
    
    namespace WcfToEntityAutomapperExample.Services
    {
        public class Service1 : IService1
        {
            public void AddPersonImplicitCast(Person person)
            {
                using (var dbContext = new PersonDbContext())
                {
                    dbContext.People.Add(person);
                    dbContext.SaveChanges();
                }
            }
    
            public List<Person> FindImplicitCast(string lastName)
            {
                using (var dbContext = new PersonDbContext())
                {
                    var foundPeopleFromDb = dbContext.People.Where(p => p.LName == lastName).ToList();
                    return foundPeopleFromDb.Select(p => (Person)p).ToList();
                }
            }
        }
    }
    

Implicit Cast Conclusion

Implicit Cast was pretty simple. I didn’t need any other classes. However, it muddied up a DataContract model class.

It is not obvious why you can add a Person where a PersonRow is needed, but it makes sense.

If I add a property or field, I’d have to add it to the cast.

My Winner

To me it is the extension method, with implicit casts a close second. I just like the simplicity of the code. A first year developer can understand and use it. I also like that it doesn’t muddy up the Model object iself like implicit operators do. Nor does it require me to create a mapping config, and initialize the mapping config.

A read a unit testing argument that unit tests won’t fail when a field is added. I had to disagree. I can put refection code in my unity test fail a test if a property is not copied. Now the reflection code is in a test project not in the production project.

My Loser

AutoMapper. It just doesn’t add the simplicity that it claims to. It by far the most complex in this scenario. Complexity != better. The gains of auto mapping Properties and Fields with the same name doesn’t outweigh the losses in readability.

Also, extension methods are far faster than AutoMapper. I didn’t do benchmarks but 7 times is what other have found. I have some data sets that take a couple of seconds to return. Times a couple of seconds by 7 and you will quickly see that such performance matters. The cost to use reflection when looping through can’t be good for you.

Also, I don’t buy into the argument that performance doesn’t matter. Performance issues pile up over time. I agree that you should not write unreadable code to optimize before you know know that readable code performs poorly. However, if two pieces of code are clear and readable and one is more performant, use the more performant. You shouldn’t make your code more complex and harder to understand to get unnecessary optimization. But with AutoMapper, you are making your code more complex and harder to understand to get less performance? How does that make sense?

Script the creation of the extension methods for objects with members named the same. You’ll be better off for it. You could even add the script to a pre-build command so the extension method is updated pre-build whenever a property is added.

Please comment and tell me which is your winner vs loser?

How to mock an Entity Framework DbContext and its DbSet properties

Entity Framework (EF) is a data access layer (DAL) that allows for easily accessing a database for your create, read, update, and delete (CRUD) actions. If you use Entity Framework, you don’t need to test your DAL CRUD actions. You just need to test your code that uses it.

With EF, you can mock your database, though it isn’t exactly easy. The database is interfaced through the DbContext. Tables are interfaces through a property of type DbSet where T is an object representing data in the table. For example, DbSet would represent your users. We can use a List to mock the database table. It would also be nice if we could easily have every table mocked by default.

Ok, now that we have established that EF 6 DbContext mocking isn’t easy, let’s change that. Let’s make it easy.

Using the following two classes, you can easily mock your DbContext with a single line of code:

var mockDbContext = EntityFrameworkMockHelper.GetMockContext<MyDbContext>();

Note: While this single line of code successfully mocks your DbContext, it doesn’t add any default data. You still have to do that work yourself, but now it should be easy because you can use the very easy to use Add method on any table.

MockDbContext.Object.People.Add(new Person{ FirstName = "John", LastName = "Doe" });

Or if you have a List of sample data already created, you can add that list with AddRange.

MockDbContext.Object.People.AddRange(SamplePeople);

And here are the two classes I wrote to help with this. I must admit, I spent over three days researching this and getting this working. So hopefully, this saves you from having to do the same.

using Moq;
using System.Collections.Generic;
using System.Data.Entity;

namespace LANDesk.Licensing.WebServices.Tests.Data
{
    public class MockedDbContext<T> : Mock<T> where T : DbContext
    {
        public Dictionary<string, object> Tables
        {
            get { return _Tables ?? (_Tables = new Dictionary<string, object>()); }
        } private Dictionary<string, object> _Tables;
    }
}
using System;
using Moq;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;

namespace LANDesk.Licensing.WebServices.Tests.Data
{
    public static class EntityFrameworkMockHelper
    {
        /// <summary>
        /// Returns a mock of a DbContext
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static MockedDbContext<T> GetMockContext<T>() where T : DbContext
        {
            var instance = new MockedDbContext<T>();
            instance.MockTables();
            return instance;
        }

        /// <summary>
        /// Use this method to mock a table, which is a DbSet{T} oject, in Entity Framework.
        /// Leave the second list null if no adds or deletes are used.
        /// </summary>
        /// <typeparam name="T">The table data type</typeparam>
        /// <param name="table">A List{T} that is being use to replace a database table.</param>
        /// <returns></returns>
        public static DbSet<T> MockDbSet<T>(List<T> table) where T : class
        {
            var dbSet = new Mock<DbSet<T>>();
            dbSet.As<IQueryable<T>>().Setup(q => q.Provider).Returns(() => table.AsQueryable().Provider);
            dbSet.As<IQueryable<T>>().Setup(q => q.Expression).Returns(() => table.AsQueryable().Expression);
            dbSet.As<IQueryable<T>>().Setup(q => q.ElementType).Returns(() => table.AsQueryable().ElementType);
            dbSet.As<IQueryable<T>>().Setup(q => q.GetEnumerator()).Returns(() => table.AsQueryable().GetEnumerator());
            dbSet.Setup(set => set.Add(It.IsAny<T>())).Callback<T>(table.Add);
            dbSet.Setup(set => set.AddRange(It.IsAny<IEnumerable<T>>())).Callback<IEnumerable<T>>(table.AddRange);
            dbSet.Setup(set => set.Remove(It.IsAny<T>())).Callback<T>(t => table.Remove(t));
            dbSet.Setup(set => set.RemoveRange(It.IsAny<IEnumerable<T>>())).Callback<IEnumerable<T>>(ts =>
            {
                foreach (var t in ts) { table.Remove(t); }
            });
            return dbSet.Object;
        }

        /// <summary>
        /// Mocks all the DbSet{T} properties that represent tables in a DbContext.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="mockedContext"></param>
        public static void MockTables<T>(this MockedDbContext<T> mockedContext) where T : DbContext
        {
            Type contextType = typeof(T);
            var dbSetProperties = contextType.GetProperties().Where(prop => (prop.PropertyType.IsGenericType) && prop.PropertyType.GetGenericTypeDefinition() == typeof(DbSet<>));
            foreach (var prop in dbSetProperties)
            {
                var dbSetGenericType = prop.PropertyType.GetGenericArguments()[0];
                Type listType = typeof(List<>).MakeGenericType(dbSetGenericType);
                var listForFakeTable = Activator.CreateInstance(listType);
                var parameter = Expression.Parameter(contextType);
                var body = Expression.PropertyOrField(parameter, prop.Name);
                var lambdaExpression = Expression.Lambda<Func<T, object>>(body, parameter);
                var method = typeof(EntityFrameworkMockHelper).GetMethod("MockDbSet").MakeGenericMethod(dbSetGenericType);
                mockedContext.Setup(lambdaExpression).Returns(method.Invoke(null, new[] { listForFakeTable }));
                mockedContext.Tables.Add(prop.Name, listForFakeTable);
            }
        }
    }
}

See descriptive Enitity Validation Errors in exception message

By default, Entity Validation Errors are not described in the DbEntityValidationException that is thrown.

If you want those displayed, here is how:

Locate your DbContext object in your project. Overload the SaveChanges() message as follows:

        public override int SaveChanges()
        {            
            try
            {
                return base.SaveChanges();
            }
            catch (DbEntityValidationException eve)
            {
                var errorMsg = new StringBuilder(eve.Message + Environment.NewLine);
                foreach (var validationResult in eve.EntityValidationErrors)
                {
                    foreach (var error in validationResult.ValidationErrors)
                    {
                        errorMsg.Append(validationResult.Entry.Entity + ": ");
                        errorMsg.Append(error.ErrorMessage + Environment.NewLine);
                    }
                }
                // Use the same exception type to avoid downstream bugs with code that catches this
                throw new DbEntityValidationException(errorMsg.ToString(), eve.EntityValidationErrors, eve);
            }
        }

I can see how the decision to not include them by default is good. Doing so could be an information disclosure security issue. However, if wrapped in a WCF service where the the entities are used as the DataContract or POCO objects, then there really isn’t much information additional information provided than what is in the wsdl.

EntityUpdater Generic helper for Entity Framework

So I am using Entity Framework (EF) more and more and I really like it. However, I’ve come across a problem when using WCF services and EF that doesn’t look to be perfectly solved.

Let me share my use case.

I have many entities but in this case, let’s use Customer and CustomerSite. I am using that Entity as a POCO object for a WCF service as well. I have two Systems. SAP and a custom C# licensing system, which is where I am using WCF and EF.

Customer
– CustomerId
– CustomerName

Customer Site
– CustomerSiteId
– CustomerId
– SiteName
– Address1
– Address2
– State
– Country
– Zip
– Website
– SapCustomerId

Let’s say that someone changes the Address for a customer in SAP. A post is made with the new Address. I tried a couple of built-in Entity Framework methods, but I’ve been unable to figure out how the front end client can update a single property without first querying over WCF for the existing values and having the client side update the changed values. To me, this seems to be a broken process. The client has to query the current object, which results in a call that has to traverse over the wire to the WCF service, then to the database through EF, then back to WCF and back over the wire to the client. Then logic has to exist on the client to update the appropriate fields. The client now needs a lot of logic it otherwise shouldn’t need.

If the client wants to update only one field, Address1, the client should be able to send an update request that contains only CustomerSiteId and Address1 with everything else should be left blank.

However, the logic to do this work on the server side seems difficult if not impossible without some data from the client. WCF gets in the way. The object is created by the WCF service and every field exists in the object and none of the fields are marked as “updated” or not. If only one field is changed, the other fields are default values in the instantiated object. How can the server know which fields are updated?

Well, if the client is sending a change and the change is not a default value, then we can ignore updates for properties that use default values. However, what if we want to change to a default value? What if, for example, the default value of Address2 is null. What if you want to change the database value back to null?

What if I want to update all the address fields but leave the CustomerId the same?

  1. Write a separate WCF method every time there is a field that might be updated by itself.

    Yeah, this is a nightmare. It isn’t really a solution.

  2. Write a method that any WCF service can call that takes in an Entity Type, an Id and a list of property names and their corresponding values.

    While this would work, it breaks the idea of using the Poco object. The ability to use a simple entity object is nice. Breaking away from the Entity object to a generic object like this seems the wrong thing to do. It would work, though. It would need client code to convert entities to this new object type that has an Id and a property value dictionary. Also, now the code is not clear. Instead of calling UpdateCustomer(customer), I would call UpdateEntity(myObject), which just isn’t as clear or as readable.

  3. Have a single generic method on the server that any WCF service method could call. The method would loop through the entity object and if a property’s value is default or null, that value isn’t updated.

    I like this because the logic is on the server. I can have UpdateCustomer and UpdateCustomerSite and each can call this method easily. The one problem, what if I want to set the value to null? This wouldn’t work.

  4. Have my WCF methods on the WCF server that takes in an IEnumerable of changed property names, then have generic code to loop through the Entity and mark the appropriate properties as “Changed”.

    I like this too, but if I only change one property to an actual value, I would really like to avoid creating the IEnumerable.

  5. Have both methods 3 and 4 above.

    This is what I am going with.

I created this class to help me with this.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Reflection;

namespace Rhyous.Db.Common
{
    public class EntityUpdater<T> where T : class
    {
        /// <summary>
        /// Updates the value of all properties with a value other than null or the default value for the class type. 
        /// </summary>
        /// <param name="entity">The entity to update</param>
        /// <param name="entry">A DbEntityEntry<T> entry object to mark which propeties are modified.</param>
        public void Update(T entity, DbEntityEntry<T> entry)
        {
            var props = from propertyInfo in entity.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(pi => !Attribute.IsDefined(pi, typeof(NotMappedAttribute)))
                        let val = propertyInfo.GetValue(entity, null)
                        where !IsNullOrDefault(val) && !IsCollection(val)
                        select propertyInfo;
            foreach (var pi in props)
            {
                entry.Property(pi.Name).IsModified = true;
            }
        }

        /// <summary>
        /// Updates the value of any property in the updatedPropertyNames list. 
        /// </summary>
        /// <param name="entity">The entity to update</param>
        /// <param name="updatedPropertyNames">The list of properties to update.</param>
        /// <param name="entry">A DbEntityEntry<T> entry object to mark which propeties are modified.</param>
        public void Update(T entity, IEnumerable<string> updatedPropertyNames, DbEntityEntry<T> entry)
        {
            var propInfoCollection = entity.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (var pi in from pi in propInfoCollection
                               from updatedPropertyName in updatedPropertyNames
                               where pi.Name.Equals(updatedPropertyName, StringComparison.CurrentCultureIgnoreCase)
                               select pi)
            {
                entry.Property(pi.Name).IsModified = true;
            }
        }

        /// <summary>
        /// Checks if any object is null or the default value for the class type. 
        /// </summary>
        /// <typeparam name="TT">The object class</typeparam>
        /// <param name="argument">The object to test for null or default.</param>
        /// <returns></returns>
        public static bool IsNullOrDefault<TT>(TT argument)
        {
            // deal with normal scenarios
            if (argument == null) return true;
            if (Equals(argument, default(TT))) return true;

            // deal with non-null nullables
            Type methodType = typeof(TT);
            if (Nullable.GetUnderlyingType(methodType) != null) return false;

            // deal with boxed value types
            Type argumentType = argument.GetType();
            if (argumentType.IsValueType && argumentType != methodType)
            {
                object obj = Activator.CreateInstance(argument.GetType());
                return obj.Equals(argument);
            }

            return false;
        }

        /// <summary>
        /// Check if an object is a collection. This is to use to ignore complex types.
        /// </summary>
        /// <typeparam name="TT">The object class</typeparam>
        /// <param name="obj">The object to test.</param>
        /// <returns></returns>
        public static bool IsCollection<TT>(TT obj)
        {
            return obj.GetType().GetInterfaces().Any(x => x.IsGenericType && x.GetGenericTypeDefinition() == typeof(ICollection<>));
        }
    }
}

Now, I can use this class in my separate WCF services as follows.

        /// <summary>
        /// Updates the value of all properties with a value other than null or the default value for the type.
        /// Any property that is not set to a value other than null or default is ignored.
        /// </summary>
        /// <param name="customerSite">The customerSite entity to update</param>
        public int UpdateSiteSimple(CustomerSite customerSite)
        {
            UpdateSite(customerSite, null);
        }

        /// <summary>
        /// Updates the value of any property in the customerSite contained in the updatedPropertyNames list. 
        /// The values of any properties not in the updatedPropertyNames list are ignored.
        /// </summary>
        /// <param name="customerSite">The customerSite entity to update</param>
        /// <param name="updatedPropertyNames">The list of properties to update.</param>
        public int UpdateSite(CustomerSite customerSite, IEnumerable<string> updatedPropertyNames)
        {
            if (customerSite == null) { throw new Exception("The customerSite cannot be null"); }

            using (var db = new MyDbContext())
            {
                db.CustomerSites.Attach(customerSite);
                var entry = db.Entry(customerSite);
                var updater = new EntityUpdater<CustomerSite>();
                if (updatedPropertyNames == null || !updatedPropertyNames.Any())
                    updater.Update(customerSite, entry);
                else
                    updater.Update(customerSite, updatedPropertyNames, entry);
                db.SaveChanges();
            }
            return customerSite.CustomerSiteId;
        }

If you like this code and find it useful please comment.

Currently, it ignores complex types and won’t update them. I could see attempting to update complex properties in the future.