Projecting on Linq to SQL, from different layers

The Need

I needed to add the possibility for upper layers like the Model/Service to make projection on Linq to SQL statements but without leaking IQueryable.

As the normal Linq to SQL projection is:

var query = from c in Cars
                    Select c.carNo, c.eName

can only be done in the layers that have the “Cars” as IQueryable, and once you return it as an IList or IEnumerable, then any projection on it will be Linq to Entities, so no projection is going to be sent to the database.

Download Code from here:

v1.1:

http://1drv.ms/1RXhy9G

v1.0:

http://1drv.ms/1M5LPOs

After de-compressing, attach the supplied database to your SQL server instance, and you might need to change the connection string “name=“PIModelConnection“” in the zClient\App.config file.

The article can be seen on CodeProject

http://www.codeproject.com/Articles/1041914/Projecting-on-Linq-to-SQL-from-different-layers

Benefits

The main purpose is:

• To speed up the results coming from SQL server.
• Lessen the size of the data if they are going to be passed through wire.
• Lessening/Or removing the need for making several DTO’s for each Entity, aka table, in the domain, by sending the request for only the required fields, and returning the full domain entities to the model, with only those fields are filled.
• One procedure for each functionality that returns Entities, but with different projections, instead of multiple procedures for each projection.
• You will get the feeling of “Oh, that helped a lot”, when you work on a lot of comboboxes and lists.

but because anonymous classes can only exist in the function/class they are created, it pose some difficulties, because the layer that wants to decide the number of fields, is different than the layer that has the DAL IQueryable EF entities. so I needed a way to send the required fields from the upper layers to the DAL IQueryable EF layers.

So after a lot of trials and errors, I moved to DLR compilation, by sending the required fields list to code snipt string, that will get compiled, then call the compiled function to project on any entity.

Trials but no success

I tried to make expressions, and lambda’s, to pass the projection list to the Linq select statement, but I couldn’t succeed, e.g.:

public Expression<Func<TEntity, IQueryable>> ProjectionExpression

public IUQueryConstraints Projection(Expression<Func<TEntity, IQueryable>> projection_)
public Expression<Func> ProjectionExpression2
 
public IEnumerable Select(IQueryable source, Expression<Func<TEntity, TResult>> selector = null)
{
   return source.Select(selector);
}
public void Select2( Expression<Func<TEntity, TResult>> selector = null)
{
   //
}

If you can know how to pass lamda’s or expression’s from another layer to the DAL layer and have the Linq to SQL work with it, then please send me your solution :)

Compiled code speed

In the code, there is some caching for the compiled methods, so they can be reused again if the same projection and the same Entity is passed, but even without it, its pretty fast.

You can see the comparison between direct call of a function vs run-time compilation, here:

http://www.codeproject.com/Tips/715891/Compiling-Csharp-Code-at-Runtime

and better results, here:
http://www.codeproject.com/Messages/4757898/Your-performance-testing-condition-for-four-method.aspx

but maybe caching the call, could have speeded up the compilation even more.

If anyone has a better and simpler Idea, I will appreciate it, if he write it in the comments :)

Solution

The sample solution, has several projects:

With this Data movement:

Compiling and Starting it, will run and show one window:

with three buttons:

• List Without Projection: List most of the POCO Entity fields, so the SQL statement sent to the DB, has all fields.
• List With Projection in Compile Time: Projects in compile time, on some fields, so the SQL statement sent to the DB, has only required fields, but the problem is that you must define a function for each projection, and only in the layers that have reference to the EntityFramework like the Repository.
• List With Projection in Runtime: Projects in compile time, on some fields, so the SQL statement sent to the DB, has only required fields, and with only one function.

The main functionality is in the “LinqProjectionC.LinqProjection” class:

Which has a predefined code string, that will  have its Linq select columns change depending on the passed fields, it will compile the code, run it, and return a List of the same passed type.

public class LinqProjection
{
    static string codeIEnumerable = @"
    using System;
    using System.Collections.Generic;
    using System.Linq;

    namespace UserClasses
    {
        public class RuntTimeClass
        {
            public static IEnumerable<%TypeName%> anyFunction(IQueryable<%TypeName%> query)
            {
                var enumerable = query.Select(c => new { %fieldsList% }).ToList();

                var queryEntities = from c in enumerable
                                        select new %TypeName%()
                                        {
                                            %fieldsList%
                                        };

                return queryEntities.ToList();
            }
        }
    }";

    public static IEnumerable projectOn<T, TCaller>(IQueryable query, String fieldsList_)
        where T : class
        where TCaller : class
    {

        var typeClass = typeof(T);
        var typeName = typeClass.FullName;
        Assembly assembly = typeClass.Assembly;
        Assembly callerAssembly = typeof(TCaller).Assembly;

        MethodInfo function = CreateFunction(fieldsList_, assembly, callerAssembly, typeName);
        var delegateFunction = (Func<IQueryable, IEnumerable>)Delegate.CreateDelegate(typeof(Func<IQueryable, IEnumerable>), function);

        IEnumerable result;
        result = delegateFunction(query);
        return result;

    }

    public static MethodInfo CreateFunction(string fieldsList_, Assembly assembly_, Assembly callerAssembly_, string typeName_)
    {
        string typeWithFields = string.Format("{0}:{1}", typeName_, fieldsList_);

        MethodInfo method = FunctionList.Singleton.getFunction(typeWithFields);

        if (method != null) return method;


        var fieldsQuery = from field in fieldsList_.Split(",".ToCharArray())
                            select ", " + field.Trim() + " = c." + field.Trim();

        StringBuilder sbFieldsList = new StringBuilder();
        foreach (string  field in fieldsQuery)
        {
            sbFieldsList.Append(field);
        }


        string finalCode = null;
        finalCode = codeIEnumerable;

        finalCode = finalCode.Replace("%TypeName%", typeName_);
        finalCode = finalCode.Replace("%fieldsList%", sbFieldsList.ToString().Substring(1));



        CSharpCodeProvider provider = new CSharpCodeProvider();
        CompilerParameters compilerParams = new CompilerParameters
        {
            GenerateInMemory = true,
            GenerateExecutable = false
        };

        SortedSet refsList = new SortedSet();

        //This will traverse the passed type assembly and add its reference assemlies to the "compilerParams"
        AssemblyReferences.Singleton.addToList(assembly_);
        AssemblyReferences.Singleton.addToList(callerAssembly_);
        AssemblyReferences.Singleton.addReferencesToParams(compilerParams);


        CompilerResults results = provider.CompileAssemblyFromSource(compilerParams, finalCode);


        if (results.Errors.HasErrors)
        {
            StringBuilder sb = new StringBuilder();

            foreach (CompilerError error in results.Errors)
            {
                sb.AppendLine(String.Format("Error ({0}): {1}", error.ErrorNumber, error.ErrorText));
            }

            throw new InvalidOperationException(sb.ToString());
        }

        Type binaryFunction = results.CompiledAssembly.GetType("UserClasses.RuntTimeClass");

        var newMethod = binaryFunction.GetMethod("anyFunction");

        FunctionList.Singleton.addFunction(typeWithFields, newMethod);

        return newMethod;
    }
}

The following function will call the creation of the compiled code, and then saves the returned MethodInfo in a cache, So if it gets called again with the same fields and type, it will use the cached MethodInfo.

public static IEnumerable projectOn<T, TCaller>(IQueryable query, String fieldsList_)
    where T : class
    where TCaller : class
{

    var typeClass = typeof(T);
    var typeName = typeClass.FullName;
    Assembly assembly = typeClass.Assembly;
    Assembly callerAssembly = typeof(TCaller).Assembly;

    MethodInfo function = CreateFunction(fieldsList_, assembly, callerAssembly, typeName);
    var delegateFunction = (Func<IQueryable, IEnumerable>)Delegate.CreateDelegate(typeof(Func<IQueryable, IEnumerable>), function);

    IEnumerable result;
    result = delegateFunction(query);
    return result;
}

Concerns

If anyone has a better, simpler, faster idea, I will appreciate it, if he can write it in the comments. :)

History

v1.3 Optimized the references cache, added more searching for references, Better graphics

v1.2 Added section “Trials but no success” + “Concerns”

V1.1 Some minor styling and headings for more clarity

V1.0 creation of the article