Contains()' Workaround Using Linq to Entities

Contains()' workaround using Linq to Entities?

Update: EF ≥ 4 supports Contains directly (Checkout Any), so you don't need any workaround.

public static IQueryable<TEntity> WhereIn<TEntity, TValue>
  (
    this ObjectQuery<TEntity> query,
    Expression<Func<TEntity, TValue>> selector,
    IEnumerable<TValue> collection
  )
{
  if (selector == null) throw new ArgumentNullException("selector");
  if (collection == null) throw new ArgumentNullException("collection");
  if (!collection.Any()) 
    return query.Where(t => false);

  ParameterExpression p = selector.Parameters.Single();

  IEnumerable<Expression> equals = collection.Select(value =>
     (Expression)Expression.Equal(selector.Body,
          Expression.Constant(value, typeof(TValue))));

  Expression body = equals.Aggregate((accumulate, equal) =>
      Expression.Or(accumulate, equal));

  return query.Where(Expression.Lambda<Func<TEntity, bool>>(body, p));
}

//Optional - to allow static collection:
public static IQueryable<TEntity> WhereIn<TEntity, TValue>
  (
    this ObjectQuery<TEntity> query,
    Expression<Func<TEntity, TValue>> selector,
    params TValue[] collection
  )
{
  return WhereIn(query, selector, (IEnumerable<TValue>)collection);
}

USAGE:

public static void Main()
{
  using (MyObjectContext context = new MyObjectContext())
  {
    //Using method 1 - collection provided as collection
    var contacts1 =
      context.Contacts.WhereIn(c => c.Name, GetContactNames());

    //Using method 2 - collection provided statically
    var contacts2 = context.Contacts.WhereIn(c => c.Name,
      "Contact1",
      "Contact2",
      "Contact3",
      "Contact4"
      );
  }
}

Workaround for LINQ to Entities does not recognize the method 'Int32 Parse(System.String)'

If Baz is a bit, you could add a where and then just use Count() as below

FooStatistics stats = (
    from f in ctx.Foo
    where <clauses here>
       and f.Baz
    group f by f.BarId
      into StatsGroup
      select new FooStatistics() {
        BarId = StatsGroup.Key,
        BazCount = StatsGroup.Count()
      }
    ).FirstOrDefault();

Using .Contains within a linq query returning a SystemException

I had the same problem the other day, seems EF doesn't support Select().Contains() without giving that error. After testing around for a bit, I ended up splitting it up in what in your case would correspond to;

var IDs = products.Select(z=>z.id);
var query = from p in db.Products
            where IDs.Contains(p.Id)
            select p;

which worked well in my case when the "products" collection was in memory anyway (ie a ToList()'ed result from the database)

The entity cannot be constructed in a LINQ to Entities query

You cannot (and should not be able to) project onto a mapped entity. You can, however, project onto an anonymous type or onto a DTO:

public class ProductDTO
{
    public string Name { get; set; }
    // Other field you may need from the Product entity
}

And your method will return a List of DTO's.

public List<ProductDTO> GetProducts(int categoryID)
{
    return (from p in db.Products
            where p.CategoryID == categoryID
            select new ProductDTO { Name = p.Name }).ToList();
}

LINQ to Entities does not recognize the method

you have a parenthesis in the wrong spot in your LINQ:

db.Products.OrderBy(x => x.Name.Skip(pageno* 8).Take(8).ToList());

You want:

db.Products.OrderBy(x => x.Name).Skip(pageno* 8).Take(8).ToList();

As it is, it's not actually performing the query until the view (and then erroring), because you aren't calling ToList on the outer query. EF only retrieves the data when it is needed.

The latter query is very common for pagination. The former doesn't really make sense.

Error: LINQ to Entities does not recognize the method 'System.String ToString(System.Object)' method occurs while string conversion

Your problem is related to the ToShortDateString call you have. One way to get rid of it would be to inspect the searchText for a potential date string before creating the expression and work with the date instead.

Expression<Func<Contact, bool>> cntExpression;

var searchDate = default(DateTime);
if (DateTime.TryParse(searchText.Trim(), out searchDate)) 
{
    cntExpression = p => p.DOB.HasValue && p.DOB == searchDate;
} 
else 
{
    cntExpression = p => p.LastName.ToLower().Trim().Contains(searchedText) ||
            p.FirstName.ToLower().Trim().Contains(searchedText) ||
            p.MiddleName.ToLower().Trim().Contains(searchedText) ||
            p.NickName.ToLower().Trim().Contains(searchedText);
}

LINQ to Entities does not recognize the method

As you've figured out, Entity Framework can't actually run your C# code as part of its query. It has to be able to convert the query to an actual SQL statement. In order for that to work, you will have to restructure your query expression into an expression that Entity Framework can handle.

public System.Linq.Expressions.Expression<Func<Charity, bool>> IsSatisfied()
{
    string name = this.charityName;
    string referenceNumber = this.referenceNumber;
    return p => 
        (string.IsNullOrEmpty(name) || 
            p.registeredName.ToLower().Contains(name.ToLower()) ||
            p.alias.ToLower().Contains(name.ToLower()) ||
            p.charityId.ToLower().Contains(name.ToLower())) &&
        (string.IsNullOrEmpty(referenceNumber) ||
            p.charityReference.ToLower().Contains(referenceNumber.ToLower()));
}

LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression

Just save the string to a temp variable and then use that in your expression:

var strItem = item.Key.ToString();

IQueryable<entity> pages = from p in context.pages
                           where  p.Serial == strItem
                           select p;

The problem arises because ToString() isn't really executed, it is turned into a MethodGroup and then parsed and translated to SQL. Since there is no ToString() equivalent, the expression fails.

Note:

Make sure you also check out Alex's answer regarding the SqlFunctions helper class that was added later. In many cases it can eliminate the need for the temporary variable.

Filtering include items in LINQ and Entity Framework

While you cannot filter a collection included via Include, you can use Select and project that collection into a filtered collection.

var rootCategoryItem = DatabaseContext.Categories
    .OrderBy(c => c.CategoryOrder)
    .Select(c => new Category()
    {
        SubCategories = c.SubCategories.Where(sub => !sub.Deleted)
            .OrderBy(sub => sub.CategoryOrder),
        c.CategoryId,
        c.CategoryName,
        //include any other fields needed here
    })
    .Single(c => c.CategoryId == 1);

linq to entities can not convert int to string

There is a SqlFunctions class available that contains many SQL-functions that may be used for LINQ. Try a look at SqlFunctions.StringConvert

Items = Mapper.Map<List<PurchaseOrder>, List<PurchaseOrderViewModel>>(
                purchaseOrderRepository
                   .GetMany(x => SqlFunctions
                                  .StringConvert((double?) x.PurchaseOrderID)
                                  .Contains(text))
                                  .ToList());

Unfortunately there is only a SqlFunctions.StringConvert(double? number) and no SqlFunctions.StringConvert(int? number). But I always convert the integer number to a double and it works as expected.

Edit: You are calling ToList prior Where. Therefore that SqlFunctions can only be called in a LINQ to Entity query any SqlFunctions after a ToList() is illegal.

Try without your GetMany Method:

purchaseOrderRepository.Set<PurchaseOrder>()
   .Where(x => SqlFunctions
       .StringConvert((double?) x.PurchaseOrderID)
       .Contains(text))

---

Related Topics