How can I run this function inside LINQ query

Question

I have function that will generate breadcrumbs format for my category e.g. Root->Children. It works because when I use it in my view it does work and doing its job. However I cannot cast it inside LINQ query.

Can someone explain how can I cast this particular function inside LINQ query? I've tried getting data and then setting it in foreach loop but it said that property is read only.

Function is Infrastructure.CategoryHelpers.Breadcrumbs({id}) it will return string.

Calling function

public dynamic List()
{
    var categories = _db.Categories.Select(x => new {
        ID = x.ID,
        Breadcrumbs = Infrastructure.CategoryHelpers.Breadcrumbs(x.ID, -1, ""), // this method cannot be translated into a store expression
        Name = x.Name,
        ItemCount = x.Items.Count
    });

    foreach (var c in categories)
    {
        // c.Breadcrumbs = Infrastructure.CategoryHelpers.Breadcrumbs(c.ID); // Value is read only
    }

    return Json(categories, JsonRequestBehavior.AllowGet);
}

Error

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

Answer 1

You will simply need to avoid executing that method on the database side; get what you need from the database, convert your query to a linq-to-objects query, and then call your method.

var categories = _db.Categories.Select(x => new {
        ID = x.ID,
        Name = x.NameLatvian,
        ItemCount = x.Items.Count
    })//restrict the columns returned from the db
    .AsEnumerable()//Switch to Linq-to-objects
    .Select(x => new {
        x.ID,
        Breadcrumbs = Infrastructure.CategoryHelpers.Breadcrumbs(x.ID, -1, ""),
        x.Name,
        x.ItemCount,
    });

Answer 2

You should be able to use AsEnumerable() which makes Linq evaluate the rest of the expression locally;

var categories = _db.Categories.Select(x => new { 
    ID = x.ID,                                    // This select is done in DB
    Name = x.Name,
    ItemCount = x.Items.Count
})
.AsEnumerable()                                   // Get result to LINQ to Objects                                   
.Select(x => new {                 
    ID = x.ID,                                    // This select is done in memory.
    Breadcrumbs = Infrastructure.CategoryHelpers.Breadcrumbs(x.ID, -1, ""),
    Name = x.Name,
    ItemCount = x.ItemCount
});

Note that you need to do all filtering (columns/Where expression/GroupBy/OrderBy) that you want done by the database before the AsEnumerable() call, since that call will fetch what is set up until then to a local IEnumerable, and do the rest of the operations on that using Linq to Objects.