RSS

Tag Archives: linq

Fix Linq Generate ‘ESCAPE’ Keyword on Contains/StartsWith/EndsWith Predicate and Cause Performance Hit

When using Linq to Entity (or Linq to SQL) with these predicates, Linq will generate ‘ESCAPE’ keyword in SQL statement which cause performance hit.

This code:

public IEnumerable GetUser(string filter)
{
    using (var db = new SomeEntities())
    {
        var result = db.User
            .Where(u => u.DisplayName.Contains(filter))
            .ToList();
    }
}

Will generate this sql statement:

SELECT [Extent1].[DisplayName] AS [DisplayName]
FROM   (SELECT [User].[DisplayName] AS [DisplayName]
        FROM   [dbo].[User] AS [User]) AS [Extent1]
WHERE  [Extent1].[DisplayName] LIKE '%garlan%' /* @p__linq__0 */ ESCAPE '~'

The `ESCAPE ‘~’` could potentially cause performance degradation.

Solution to this issue is to pass in constant into Contains predicate. It’s easier said than done.
If we have method like `GetUser` above which take ‘filter’ parameter, it’s not possible to convert to constant.
To overcome this, we have to create method that return predicate with constant.

This solution is proposed here.

Summary is to create extension class.

using System;
using System.Linq.Expressions;

namespace Blahblahblah
{
    public static class PredicateConstantCreator
    {
        public static Expression EmbedConstant(this Expression expression, TConstant constant)
        {
            var body = expression.Body.Replace(expression.Parameters[1], Expression.Constant(constant));

            return Expression.Lambda(body, expression.Parameters[0]);
        }

        private static Expression Replace(this Expression expression, Expression searchEx, Expression replaceEx)
        {
            return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);
        }

        internal class ReplaceVisitor : ExpressionVisitor
        {
            private readonly Expression from;
            private readonly Expression to;

            public ReplaceVisitor(Expression from, Expression to)
            {
                this.from = from;
                this.to = to;
            }

            public override Expression Visit(Expression node)
            {
                return node == this.from ? this.to : base.Visit(node);
            }
        }
    }
}

And use it like this:

Expression predicate = (item, filterTerm) => item.DisplayName.Contains(filterTerm);
var result = User
    .Where(predicate.EmbedConstant(filter))
    .ToList();

Linq will generate sql statement like this:

SELECT [Extent1].[DisplayName] AS [DisplayName]
FROM   (SELECT [User].[DisplayName] AS [DisplayName]
        FROM   [dbo].[User] AS [User]) AS [Extent1]
WHERE  [Extent1].[DisplayName] LIKE '%garlan%' /* @p__linq__0 */

Alternative solution is to add function to generated XML file in EDMX. This solution is lay out here.

Advertisements
 
Leave a comment

Posted by on October 23, 2018 in General

 

Tags: , , , , ,

What is the Best and Fastest Way to Flatten Data?

Given the following data set, what’s the best and fastest way to flatten them? This can be achieved with LINQ, SQL, C#, or whatever else you prefer.

001 12/5/2013 113 143 11143 44 673
001 12/6/2013 113 143 11143 44 673
001 12/7/2013 113 143 11143 44 673
001 12/8/2013 113 143 11143 44 673
001 12/9/2013 113 143 11143 44 673
001 12/10/2013 113 143 11143 44 673
002 12/5/2013 113 143 11143 44 673
002 12/6/2013 113 143 11143 44 673
002 12/7/2013 113 143 11143 44 673
002 12/8/2013 113 143 11143 44 673
002 12/9/2013 113 143 11143 44 673
002 12/10/2013 113 143 11143 44 673

Desired result:

001 12/5/2013 113 143 11143 44 673 12/6/2013 113 143 11143 44 673 12/7/2013 113 143 11143 44 673 12/8/2013 113 143 11143 44 673 12/9/2013 113 143 11143 44 673 12/10/2013 113 143 11143 44 673
002 12/5/2013 113 143 11143 44 673 12/6/2013 113 143 11143 44 673 12/7/2013 113 143 11143 44 673 12/8/2013 113 143 11143 44 673 12/9/2013 113 143 11143 44 673 12/10/2013 113 143 11143 44 673
 
Leave a comment

Posted by on December 3, 2014 in General

 

Tags: , , ,

Cross Reference and Checking for CRUD Operations

Whenever you allow users to modify a list of data from a grid on client side, your server side code can get hairy, trying to perform create / update / delete operations. Part of this is knowing what’s being change, what user has created and deleted, and how we indicate each record.

Common approach could be marking each record the state they are in, for example, when a user creates a new record, the new record is marked as ‘Created’. Then, server-side code handle each one of them based on the state.

Cross reference and checking technique is used to handle this kind of situation. It can be applied to anything that needs create / update / delete at the same time (by that I don’t mean async). I am sure this technique is out there and widely popular already, but for those who haven’t seen it, here it is.

The idea is simple. First, you get all data from database and user input. Then, cross checking them to come up with three lists:

  • What exists only in database
  • What exists only in user input
  • What exists in both

What exists only in database is what needs to be deleted.
What exists only in user input is what needs to be created.
What exists in both is what needs to be updated.

The order in which you perform the operation is not really matter in most cases.

C# Linq samples. Note that in below samples, when my data is deleted from database, it’s being soft-deleted (in IsDeleted column). You can perform hard delete if you wish, the options are not related to this technique.

Get All Data

// Get all data from database
var accountsInDb = _context.GetAllAccounts();
// Get all user input data
var accountsInModel = model.Accounts;

Delete operation

var onlyExistInDb = accountsInDb.Except(accountsInModel).ToList();

onlyExistInDb.ForEach(x => _context.Remove(x));

_context.SaveChanges();

Create operation

var onlyExistInModel = accountsInModel.Except(accountsInDb).ToList();

onlyExistInModel.ForEach(x => {
    _context.Add(new Account {
        AccountId = x.Id,
        AccountName = x.Name,
        AccountRefNumber = x.RefNumber
    });
});

_context.SaveChanges();

Update operation

var existInBoth = accountsInModel.Intersect(accountsInDb).ToList();

existInBoth.ForEach(x => {
    var _account = _context.Accounts.Where(y => y.AccountId == x.AccountId);
    _account.Name = x.Name;
    _account.RefNumber = x.RefNumber;
});

_context.SaveChanges();
 
Leave a comment

Posted by on March 14, 2013 in General

 

Tags: , , ,

LINQ Select Distinct in Flat Data

Have a flat data schema, such as

</pre>
public class Account {
    public int ID {get;set;}
    public string Name {get;set;}
    public int UserID {get;set;}
}

If I have a list of this flat data, sometime it’s necessary to get unique data. This is how I go about doing it

var nonUnique = new List<Account>{
    new Account {
        ID = 1,
        Name = "A",
        UserID = 1
    },
    new Account {
        ID = 1,
        Name = "A",
        UserID = 2
    },
    new Account {
        ID = 2,
        Name = "B",
        UserID = 4
    }
};

var unique = (from m in nonUnique select new { Id = m.ID, Name = m.Name }).Distinct();

Console.Write(nonUnique);
Console.Write(unique);

unique variable contains distinct data of non-unique.

The output is following:

linq-select-distinct-in-flat-data

 
Leave a comment

Posted by on February 20, 2013 in General

 

Tags: ,

Distinct in LinqJS

I was fiddling around Distinct() function in LinqJS, a library to emulate LINQ functions in JavaScript. If you haven’t played with it, head to http://linqjs.codeplex.com/

Anyway, the Distinct() function is little different than one in C#. In LinqJS, you would pass in the predicate to the Distinct() function while in C#, you would call Distinct() after another method. Here’s the example:

C#

var u = dataArray.Select(x => x.AccountID).Distinct();

LinqJS


// Get non-duplicate objects.
var u = Enumerable.From(dataArray).Distinct(function (x) { return x.AccountID; }).Select(function (a) { return a; }).ToArray();

 
Leave a comment

Posted by on January 16, 2013 in General

 

Tags: ,

Impedance Mismatch

In object oriented programming, there is one problem called Impedance Mismatch. The problem occurs when there is a mismatch / differences / gap between objects in an application and representation of data in storage.

Some differences that can cause Impedance Mismatch are:

  • Data type
  • Structural and integrity
  • Manipulative
  • Transactional

Features in programming language and framework can help reduce Impedance Mismatch, such as LINQ and Entity Framework in .Net framework.

Reference: Wikipedia

 
Leave a comment

Posted by on March 6, 2012 in General

 

Tags: , ,

Using Custom Method in LINQ To Entity Framework (LINQ Method Cannot be Translated into a Store Expression)

I got this error when using a custom method in Linq to Entity Framework:

Here’s my code:

using (var context = DataObject.CreateContext())
{
    var _credits = context.CreditEntities;

    var _creditsSorted = _credits.AsQueryable().OrderBy(sortExpression, null);

    return _creditsSorted.Select(x => Mapper.Map(x)).ToList();
}

The error occurs on line 7.

The problem is caused by calling Mapper.Map() from the object that doesn’t recognize (or can’t implement) it, in this case Entity Framework (LINQ to Entity Framework).

To solve the problem, I can’t use Entity Framework to call my custom method. So, the object needs to be converted. In my case (line 3):

var _credits = context.CreditEntities.ToList();
 
Leave a comment

Posted by on July 31, 2011 in General

 

Tags: , , ,

 
%d bloggers like this: