Entity Framework ToList() in nested type (LINQ to Entities does not recognize the method)

Update:
The issue is described here:
http://entityframework.codeplex.com/workitem/808
I’ve made a fork of EF source which solves this issue @
http://entityframework.codeplex.com/SourceControl/network/forks/brentmckendrick/Issue808/changeset/b18e48b3e51f
however more work could be done to enhance the code by allowing for ToArray and ToDictionary.

So today I have a suggestion for an improvement to the Entity Framework.

First let me set the scenario:

Say I have an entity called Person which has many Addresses, as shown below.

public class Person
{
    // .. lots of fields ..
    public ICollection<Address> Addresses { get; set; }
}

public class Address
{
    // .. lots of fields ..
}

Now say I would like to create a projection so that I do not return all of the information contained in the Person and Address models.
I could write this:

Context.Persons
.Select(p => new
{
    Addresses = p.Addresses.Select(m => new
    {
        // address properties
    })
    // other person properties
}).ToList();

This would obviously give me an anonymous type (A1) which contains a property of List'(T) called Addresses where T is anonymous type (A2). The key here is that the type instantiated is in fact a List'(T)

Now lets say I have a set of data transfer objects. One is PersonDTO which has a property of type List'(T) where T is an AddressDTO as shown below.

public class PersonDTO
{
    // .. a few fields ..
    public List<AddressDTO> Addresses { get; set; }
}

public class Address
{
    // .. a few fields ..
}

I want to map a person to a PersonDTO. This is shown here:

Context.Persons
.Select(p => new PersonDTO
{
    Addresses = p.Addresses.Select(m => new AddressDTO
    {
        // address properties
    }).ToList() // not supported
    // other person properties
}).ToList();

I get an Exception : System.NotSupportedException – “LINQ to Entities does not recognize the method ‘System.Collections.Generic.List`1[t] ToList[t](System.Collections.Generic.IEnumerable`1[t])’ method, and this method cannot be translated into a store expression.”

Now all of you LINQ to Entities guys are screaming “You can’t call ToList() inside of a nested projection!”. OK, I can see that the ToList() will not be able to be translated to an SQL query, obviously if I leave it out then my code will not compile since PersonDTO.Addresses is a List'(T). If I change the PersonDTO.Addresses field to an IEnumerable and remove the ToList() then it works! (and by reflecting the runtime type it is in fact populated with a generic list). So the list IS being instantiated anyway.

Now I know this projection could be done in memory and I would not have any problems calling ToList() because it would not need to be translated into an SQL query, however this defeats the purpose as I am trying to write an SQL query.

Now to the point!:

– An anonymous type can be created with a nested collection property of type List.
– A concrete type can be created with a nested collection of type IEnumerable and a List will be created and populated for this variable
– A concrete type with a nested property collection of a List (or any derivative of type ICollection) can NOT be projected to (because of the ToList() issue)

Now it seems to me that since in all of these cases a List is created anyway that perhaps when the expression is mapped to an SQL function the call to the nested ToList() should be ignored? Simply by doing this all of the problems would be solved. (Please correct me if this assumption is incorrect). Better yet what if it WASN’T ignored but removed from the SQL expression and stored for later use so that we could also call ToArray(), ToList(), etc. and change the type which is being returned from the EF materialization? Eg.

Context.Persons
.Select(p => new PersonDTO
{
    Addresses = p.Addresses.Select(m => new AddressDTO
    {
        // address properties
    }).ToArray()
    // other person properties
}).ToList();
Advertisements

Generic Repository: Fake IDbSet implementation update (Find Method & Identity key)

UPDATE (again) Just a quick one: see https://github.com/refactorthis/GraphDiff/blob/master/EFDetachedUpdate/DetachedUpdate/DbContextExtensions.cs on line 209 for a replacement GetKeyProperties method which allows for convention and fluent API mapped keys (You no longer need to annotate your model with KeyAttribute)

UPDATE: Thanks to Eli Weinstock-Herman for pointing out the fact that Find should return null if no result is found (SingleOrDefault instead of Single). Cheers Eli.

Hey guys,

I’ve been back in the coding seat lately creating a new generic repository for a system that we are building. I’ve made some improvements to the FakeDbSet that I posted about earlier Here.

I want to add some notes to the previous post which are long enough they warrant a new post. Firstly, IT IS MUCH EASIER if you do not use foreign keys in your objects but instead use ‘association’ object references. This means you will not have to co-ordinate two different fields when setting up test data. Of course EF does this for you when connected to the database but in memory you would have to do this yourself.

Secondly the implementation of find was quite hard, though I believe I have come up with an elegant generic solution. If you look at the IDbSet documentation MSDN you will see that Find() expects the keys to passed in “the same order that they are defined in the model”.

If I use reflection to find my key properties I can then iterate through the keys and ensure that each object given in the find method equals the value of that key, as shown below.


        private List<PropertyInfo> _keyProperties;

        public virtual T Find(params object[] keyValues)
        {
            if (keyValues.Length != _keyProperties.Count)
                throw new ArgumentException("Incorrect number of keys passed to find method");

            IQueryable<T> keyQuery = this.AsQueryable<T>();
            for (int i = 0; i < keyValues.Length; i++)
            {
                var x = i; // nested linq
                keyQuery = keyQuery
                   .Where(entity => _keyProperties[x].GetValue(entity, null).Equals(keyValues[x]));
            }

            return keyQuery.SingleOrDefault();
        }

        private void GetKeyProperties()
        {
            _keyProperties = new List<PropertyInfo>();
            PropertyInfo[] properties = typeof(T).GetProperties();
            foreach (PropertyInfo property in properties)
            {
                foreach (Attribute attribute in property.GetCustomAttributes(true))
                {
                    if (attribute is KeyAttribute)
                    {
                        _keyProperties.Add(property);
                    }
                }
            }
        }

Now thirdly I wanted the FakeDbSet to act like the database and use an identity column for properties that are ints and marked with the [Key] attribute. I made these changes here

private int _identity = 1;

private void GenerateId(T entity)
{
     // If non-composite integer key
     if (_keyProperties.Count == 1 && _keyProperties[0].PropertyType == typeof(Int32))
         _keyProperties[0].SetValue(entity, _identity++, null);
}

  public T Add(T item)
  {
      GenerateId(item);
      _data.Add(item);
      return item;
 }

Now of course this is being done in the Add method not the commit method as the database would. For my purposes this makes no difference. If however you want the key generation to be done on commit then you need to keep an un-comitted list inside of the FakeDbSet and then when commit is called you would iterate the list generating id’s for each element and then adding them to the ‘comitted’ list.

Here is the new FakeDbSet implementation


public class FakeDbSet<T> : IDbSet<T> where T : class
    {
        private readonly HashSet<T> _data;
        private readonly IQueryable _query;
		private int _identity = 1;
        private List<PropertyInfo> _keyProperties;

        private void GetKeyProperties()
        {
            _keyProperties = new List<PropertyInfo>();
            PropertyInfo[] properties = typeof(T).GetProperties();
            foreach (PropertyInfo property in properties)
            {
                foreach (Attribute attribute in property.GetCustomAttributes(true))
                {
                    if (attribute is KeyAttribute)
                    {
                        _keyProperties.Add(property);
                    }
                }
            }
        }

		private void GenerateId(T entity)
		{
            // If non-composite integer key
            if (_keyProperties.Count == 1 && _keyProperties[0].PropertyType == typeof(Int32))
                _keyProperties[0].SetValue(entity, _identity++, null);
		}

        public FakeDbSet(IEnumerable<T> startData = null)
        {
            GetKeyProperties();
			_data = (startData != null ? new HashSet<T>(startData) : new HashSet<T>());
            _query = _data.AsQueryable();
        }

        public virtual T Find(params object[] keyValues)
        {
            if (keyValues.Length != _keyProperties.Count)
                throw new ArgumentException("Incorrect number of keys passed to find method");

            IQueryable<T> keyQuery = this.AsQueryable<T>();
            for (int i = 0; i < keyValues.Length; i++)
            {
                var x = i; // nested linq
                keyQuery = keyQuery.Where(entity => _keyProperties[x].GetValue(entity, null).Equals(keyValues[x]));
            }

            return keyQuery.SingleOrDefault();
        }

        public T Add(T item)
        {
            GenerateId(item);
            _data.Add(item);
            return item;
        }

        public T Remove(T item)
        {
            _data.Remove(item);
            return item;
        }

        public T Attach(T item)
        {
            _data.Add(item);
            return item;
        }

        public void Detach(T item)
        {
            _data.Remove(item);
        }

        Type IQueryable.ElementType
        {
            get { return _query.ElementType; }
        }

        Expression IQueryable.Expression
        {
            get { return _query.Expression; }
        }

        IQueryProvider IQueryable.Provider
        {
            get { return _query.Provider; }
        }

        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
        {
            return _data.GetEnumerator();
        }

        IEnumerator<T> IEnumerable<T>.GetEnumerator()
        {
            return _data.GetEnumerator();
        }

        public T Create()
        {
            return Activator.CreateInstance<T>();
        }

        public ObservableCollection<T> Local
        {
            get
            {
                return new ObservableCollection<T>(_data);
            }
        }

        public TDerivedEntity Create<TDerivedEntity>() where TDerivedEntity : class, T
        {
            return Activator.CreateInstance<TDerivedEntity>();
        }
    }

Hope this code is useful to someone else ūüôā

SaaS Multitenant ASP.NET 4.0 – Step 1 Introduction

Introduction

This is the first in what I have planned to be a series of posts all about moving your application(s) to a multi-tenant architecture.

I have noticed that there really isn’t enough being done in the way of frameworks and tutorials in the area of multitenancy, especially using .NET and the Entity Framework. Please learn with me as I tackle the many problems I am facing right now moving our suite of products to a more Saas friendly environment.

Software as a service

I’m sure most have heard the buzz phrase “Software as a service (SaaS)”, I won’t go into too many details here about the model itself, a quick google search will find many articles that cover the concept much better than I could here. Basically it can be¬†very cost effective for ISV’s as it pushes configuration over customisation, low running costs, subscription paid services and it fits perfectly in the cloud. I really like the concept as a developer because it means less custom coding for client specific functionality,¬†makes it much easier to manage versions of code (when clients are on the same branch) and it¬†is extendable by its nature so gives room for evolutions of the software’s purpose.

This series is aimed at the actual implementation of a multi-tenant architecture using .NET 4.0 technologies. This essentially means allowing multiple clients (or tenants) to run on the same codebase with their own custom fields, themes, workflows and other features.

When implementing this architecure some problems will be faced such as:

  • How many databases will we need? 1 per client, 1 database for all clients?, etc.
  • How will we manage custom client fields that need to persisted to that database?
  • How will the UI change to reflect those extra fields?
  • How do we manage custom reporting?
  • Where will we place business logic that will change per client?
  • How do we inject extra business logic and validation¬†for client specific decisions?
  • How will we manage custom client workflows?
  • How do offer particular features to only those clients who have paid for them?
  • If a client wants to change a theme to suit their intranet’s theme can we allow for this?
  • How will we handle¬†users, login/logout methods, roles and permissions so that the security aspect of the system is abstracted well enough to be easily¬†defined per client? How will the our user’s manage their security roles and users?
  • Is it worth doing all this work, or should we just make client changes on their own versions of code? (Works for a certain threshold of clients to resources and¬†for systems that don’t need much in the way of customisation)
  • How do we build this architecture in the most elegant way. And when I say elegant I mean without needing 20 layers of abstraction

If your still with me you’ll see that multi-tenancy poses quite a few questions and¬†is not something that can be done overnight. The benefits are that you get to manage only one branch of code and can have quite a few clients on there happy without needing to do some serious customisation of the product and without needing to manage a seperate version of code for each client (headache!).

The cons are obviously the upfront cost in time and effort. I’m am facing this problem now and hopefully this guide and the relevant source will help those looking to solve this problem using .NET technolgies.

So stay with me and lets work through this headache together. Up next will be our data access layer and database design.

Cheers