The LINQ expression 'FirstOrDefault()' could not be translated and will be evaluated locally

Question

In seeding, I use collection of jsons to read it and insert in DB.

I using this function

private void Seed<T, T2>(DbSet<T> dataSet, Func<T, T2> uniquProperty) where T : class, ISeedableEntity<T>
{
    var separtor = Path.DirectorySeparatorChar;
    var tableName = Model.FindEntityType(typeof(T)).SqlServer().TableName;
    var baseDir = Assembly.GetExecutingAssembly().Location;
    baseDir = Path.GetDirectoryName(baseDir);
    var filePath = $"{baseDir}{separtor}Data{separtor}{tableName}.json";
    if (File.Exists(filePath))
    {
        var data = File.ReadAllText(filePath);
        var items = JsonConvert.DeserializeObject<List<T>>(data);
        foreach (var item in items)
        {
            var found = dataSet.Where(
                a => uniquProperty(a).Equals(uniquProperty(item))).FirstOrDefault();

            if (found != null)
            {
                found = found.Update(item);
                dataSet.Update(found);
            }
            else
                dataSet.Add(item);
        }
        SaveChanges();
    }
}

And call it as this code.

Seed(CreditRatings, a => a.Id);

`Func uniquProperty` needs to be an `Expression` | https://stackoverflow.com/questions/793571/why-would-you-use-expressionfunct-rather-than-funct — Rand Random, Apr 15 '22 at 12:25
`a => uniquProperty(a).Equals(uniquProperty(item))` is the problem, EF (I assume you are using it) will not be able to translate `uniquProperty` call into SQL. You need to change `uniquProperty` parameter to `Expression>` and build whole expression for `Where` from it. — Guru Stron, Apr 15 '22 at 12:25
@MenaSamer - you shouldn't do it this way, because with the foreach you are than loading everything from the database into your local memory, and evaluate it locally, but instead you should use expression to let the evaluation happen on the database server - imagine you have millions of records with the foreach you would load each and every row, every row would need to move from database server to your local machine, eg. over the internet or intranet, it would need to be transformed from a database reader to an object (materialized) and than finally evaluated, so please use an expression — Rand Random, Apr 15 '22 at 12:36

score 1 · Accepted Answer · answered Apr 15 '22 at 12:27

The problem (I believe) is in the uniqueProperty delegate, which is declared as a Func delegate. LINQ cannot translate compiled functions into parts of the SQL instruction, and therefore gives up the effort, letting the predicate resolve at the calling end.

The direct result of such unfortunate even is that all the data from the database would be transferred to the caller prior to subduing them to the function.

You may try by accepting an expression evaluating to bool in place of the Func delegate.

private void Seed<T, T2>(DbSet<T> dataSet, Expression<Func<T, T, bool>> predicate)
where T : class, ISeedableEntity<T>
{
    // ...
}

The predicate would have to complete comparison of unique properties:

Expression<Func<T, T, bool>> seed = (a, b) => a.Id.Equals(b.Id);

It is probably possible to incorporate an expression which isolates an equatable property as well, and then to perform the equality test directly in the Where clause, but that would require a bit more work.

The LINQ expression 'FirstOrDefault()' could not be translated and will be evaluated locally

1 Answers1