FindAsync and Include LINQ statements

Question

The code I have got so far works fine

public async Task<ActionResult> Details(Guid? id)
{
    if (id == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }

    ItemDetailModel model = new ItemDetailModel();
    model.Item = await db.Items.FindAsync(id);
    if (model.Item == null)
    {
        return HttpNotFound();
    }

    return View(model);
}

But I want to include 1 table more and cannot use FindAsync

public async Task<ActionResult> Details(Guid? id)
{
    if (id == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }

    ItemDetailModel model = new ItemDetailModel();
    model.Item = await db.Items.Include(i=>i.ItemVerifications).FindAsync(id);

    if (model.Item == null)
    {
        return HttpNotFound();
    }           

    return View(model);
}

So I am facing this error

Severity Code Description Project File Line Suppression State Error CS1061 'IQueryable' does not contain a definition for 'FindAsync' and no extension method 'FindAsync' accepting a first argument of type 'IQueryable' could be found (are you missing a using directive or an assembly reference?)

Any clue how to fix it?

[This post](https://social.msdn.microsoft.com/Forums/en-US/270ba03d-e89e-4db5-8e45-6d7f900d9442/dbsetentity-becomes-objectquery-type-in-linq-to-entities-query?forum=adonetefx) may give you some answers. It looks like `.Include` returns an `ObjectQuery` instead of a `DbSet` (which has the `FindAsync` method). — rory.ap, Nov 01 '16 at 13:00
Check out this [question](http://stackoverflow.com/questions/21817569/use-of-include-with-async-await) — Jeffrey Patterson, Nov 01 '16 at 13:08

Ivan Stoev · Accepted Answer · 2016-11-01T13:13:49.490

140

The simplest is to use FirstOrDefaultAsync or SingleOrDefaultAsync instead:

model.Item = await db.Items.Include(i => i.ItemVerifications)
    .FirstOrDefaultAsync(i => i.Id == id.Value);

The reason you are getting the error is because Find / FindAsync methods are defined for DbSet<T>, but the result of Include is IQueryable<T>.

Another way is to combine FindAsync with explicit loading:

model.Item = await db.Items.FindAsync(id);
if (model.Item == null)
{
    return HttpNotFound();
}
await db.Entry(model.Item).Collection(i => i.ItemVerifications).LoadAsync();

edited Nov 01 '16 at 13:13

answered Nov 01 '16 at 13:02

Ivan Stoev

195,425
15
312
343

1

You could improve this answer by explaining why what he tried didn't work (see my comment under the question). – rory.ap Nov 01 '16 at 13:04
2

used the second option. chears – Tzvi Gregory Kaidanov Jul 24 '17 at 17:18
var entity = await context.Entities .Include("field") .FirstOrDefaultAsync(e => e.Id == id); – NiB Jun 08 '22 at 03:56
1

Considering the [debate over which is faster, Find vs FirstOrDefault](https://stackoverflow.com/q/14032709/1219280), which seems to point `Find` is faster, doing so negatively impacts performance. – Veverke Feb 08 '23 at 11:01
1

@Veverke The question here is **not** which one is faster, but which one supports loading related data. `Find` doesn't, so any further discussion is useless. Performance concerns are ok, but functionality is first. – Ivan Stoev Feb 08 '23 at 13:59

score 13 · Answer 2 · edited Nov 23 '18 at 10:38

If you are using a generic repository and you don't know the PK at runtime, this approach can help:

public interface IGenericRepository<TEntity> where TEntity : class
{
    Task<TEntity> Get(int id, string[] paths = null);
}

public class GenericRepository<TEntity> : IGenericRepository<TEntity> where TEntity : class
{
    private readonly ApplicationDbContext _context;
    private readonly DbSet<TEntity> _dbSet;

    public GenericRepository(ApplicationDbContext context)
    {
        _context = context;
        _dbSet = _context.Set<TEntity>();
    }

    public async Task<TEntity> Get(int id, string[] paths = null)
    {
        var model = await _dbSet.FindAsync(id);
        foreach (var path in paths)
        {
            _context.Entry(model).Reference(path).Load();
        }
        return model;
    }
}

I would expect this to make 2 trips to the database instead of the one when you use Include on the DbSet. It is a shame that FindAsync didn't offer the ability to include additional fields directly. — fubaar, Nov 23 '18 at 10:34

score 2 · Answer 3 · edited Jul 17 '20 at 11:53

When you program using solid principles and domain design then use generics. The Repository pattern uses a generic class. I pass a lambda express to the GetObjectsQueryable function. I have setup lazy loading to be on, using code first handle bars. However, I am moving away from lazy loading and implement a microservice architecture. The include table is a string and you can use the nameof(xxclass) function to ensure the correct name. The function returns and IQueryable results. The repository class methods can be used by its derived class enhance the method is protected. This is a dotnet.core demonstration.

public class Repository
    where T : class
{
    public IQueryable<T> GetObjectsQueryable(Expression<Func<T, bool>> predicate, string includeTable="")
    {
        IQueryable<T> result = _dbContext.Set<T>().Where(predicate);
        if (includeTable != "")
            result = result.Include(includeTable);

        return result;
    }
}

In your OnConfiguring(DbContextOptionsBuilder optionsBuilder) method of entity framework add the lazy loading proxy with the following call optionsBuilder.UseLazyLoadingProxies().UseSqlServer(connectionString); — Golden Lion, Dec 21 '19 at 12:18

FindAsync and Include LINQ statements

3 Answers3

Linked

Related