how use lambda expression on entity framework with high performance

Question

I work on c# winforms. I use entity framework 6.

In my solution I have 2 project with A.BLL & A.DAL Names. (A.DAL added on A.BLL References)

A.DAL Project I have under method:

public decimal Sum(Func<ml_doc, bool> predicate, Func<ml_doc, decimal> sumColumn)
{
    try
    {
        using (dbEnteties = new Entities(Connection.CustomEntityConnection))
        {
            dbEnteties.ContextOptions.LazyLoadingEnabled = true;
            var dbe = dbEnteties.ml_doc;
            return dbe.Where(predicate).Sum(sumColumn);
        }
    }
    catch (Exception exp)
    {
        throw exp;
    }
}

A.BLL project I have under method (that use Sum method on A.DAL project) :

public decimal GetSum(long AccId, int? CntId, short BranchId, int BeginNumber)
{
    try
    {
        using (dal = new DAL.DocDA())
        {
            // Sum method referenced from A.DAL project
            return dal.Sum(
                x =>
                x.acc_id == AccId &&
                x.cnt_id.Equals(CntId) &&
                x.ml_doc_hdr.branch_id == BranchId &&
                x.ml_doc_hdr.number >= BeginNumber
                ,
                y => y.price);
        }
    }
    catch (Exception exp)
    {
        throw exp;
    }
}

When I use GetSum method (in A.BLL project) I get under exception:

There is already an open DataReader associated with this Command which must be closed first.

To solve this exception I add MultipleActiveResultSets=True to my connection string, this method works very slowy (for example 3 seconds).

I create under method on A.DAL project :

public decimal Sum2(long AccId, int? CntId, short BranchId, int BeginNumber)
{
    try
    {
        using (dbEnteties = new Entities(Connection.CustomEntityConnection))
        {
            dbEnteties.ContextOptions.LazyLoadingEnabled = true;
            var resultQuery = dbEnteties.ml_doc.Where(
                x =>
                x.acc_id == AccId &&
                x.cnt_id.Equals(CntId) &&
                x.ml_doc_hdr.branch_id == BranchId &&
                x.ml_doc_hdr.number >= BeginNumber
                );

            if (resultQuery.Count() != 0)
            {
                return resultQuery.Sum(x => x.price);
            }

            return 0;
        }
    }
    catch (Exception exp)
    {
        throw exp;
    }
}

When I use upper method (Sum2) this work fine and very fast (for example 0.003 second)

What is diffrence between Sum2 (on A.DAL project) & GetSum (on A.BLL projetc) Methods (It seems there are Same)?

How can change GetSum Method to work with high performance?

Answer 1

This one:

public decimal Sum(Func<ml_doc, bool> predicate, Func<ml_doc, decimal> sumColumn)
{
    try
    {
        using (dbEnteties = new Entities(Connection.CustomEntityConnection))
        {
            dbEnteties.ContextOptions.LazyLoadingEnabled = true;
            var dbe = dbEnteties.ml_doc;
            return dbe.Where(predicate).Sum(sumColumn);
        }
    }
    catch (Exception exp)
    {
        throw exp;
    }
}

loads the full ml_doc table locally from the SQL server, and then executes the Where() and the Sum() operations locally.

This because your LINQ expression uses two Func<> delegates, so instead of using

• Queryable.Where(this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate)
• Queryable.Sum(this IQueryable<TSource> source, Expression<Func<TSource, decimal>> selector)

it uses

• Enumerable.Where(this IEnumerable<TSource> source, Func<TSource, bool> predicate)
• Enumerable.Sum(this IEnumerable<TSource> source, Func<TSource, decimal> selector)

Try changing it to:

public decimal Sum(Expression<Func<ml_doc, bool>> predicate, Expression<Func<ml_doc, decimal>> sumColumn)

The Sum2 method instead, by using directly some lambda functions, uses the Queryable.* methods, because the lambda functions are interpreted as Expression<>.