0

I have two tables: Products and Product Images

I am attempting to write a query that brings back Products and for each product a list of image names that are associated but keep receiving an error (shown below).

The entities looks like this:

public class ProductDto
{
    public int Id { get; set; }

    public string Name { get; set; }

    public long ProductNumber { get; set; }

    public decimal? Size { get; set; }

    public string SizeMeasurement { get; set; }

    public decimal? Pack { get; set; }

    public List<string> PhotoFileNames { get; set; }
}

The query that is erroring out is shown here:

var query = from p in _productRepository.GetAll()
        let PhotoFileNames =  _productPhotoMapRepository.GetAll().DefaultIfEmpty()
            .Where(e => e.ProductId == p.Id).Select(e => e.FileName).ToList()
    where
    (string.IsNullOrWhiteSpace(input.Filter) || p.Name.Contains(input.Filter))
    select new
    {
        p.Id,
        p.ProductNumber,
        p.Name,
        p.Size,
        p.SizeMeasurement,
        p.Pack,
//I've also tried doing this but get same error:
//PhotoFileNames = _productPhotoMapRepository.GetAll().DefaultIfEmpty()
//                     .Where(e => e.ProductId == p.Id).Select(e => e.FileName).ToList()
        PhotoFileNames
    };

    var productList = await query.ToListAsync();

The error I am receiving is:

System.ArgumentException: Expression of type 'System.Collections.Generic.IAsyncEnumerable`1[System.String]' cannot be used for parameter of type 'System.Collections.Generic.IEnumerable`1[System.String]' of method 'System.Collections.Generic.List`1[System.String] ToList[String](System.Collections.Generic.IEnumerable`1[System.String])'
Parameter name: arg0
   at System.Dynamic.Utils.ExpressionUtils.ValidateOneArgument(MethodBase method, ExpressionType nodeKind, Expression arguments, ParameterInfo pi, String methodParamName, String argumentParamName, Int32 index)
   at System.Linq.Expressions.Expression.Call(MethodInfo method, Expression arg0)
   at System.Linq.Expressions.MethodCallExpression1.Rewrite(Expression instance, IReadOnlyList`1 args)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.RelationalEntityQueryableExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitAndConvert[T](ReadOnlyCollection`1 nodes, String callerName)
   at Remotion.Linq.Parsing.RelinqExpressionVisitor.VisitNew(NewExpression expression)
   at System.Linq.Expressions.NewExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.RelationalEntityQueryableExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)

I am attempting to avoid using a for loop for performance reasons. If I omit the PhotoFileNames completely, products return without any issues.

What would be the correct way to get the desired result of every product and all of the image file names that are stored in different sql table?

Edit: Added DB Context and Model Classes

public class Product
    {
        public int Id { get; set; } //Aka productId

        public string Name { get; set; }

        public long ProductNumber { get; set; }

        public decimal? Size { get; set; }

        public string SizeMeasurement { get; set; }

        public decimal? Pack { get; set; }
    }

       public class ProductPhotoMap 
        {
           public virtual long ProductPhotoId {get;set;}

            public virtual int DisplayOrder { get; set; }

            public virtual bool IsDefault { get; set; }     

            public virtual int ProductId { get; set; }
            public Product Product { get; set; }

            public virtual string FileName { get; set; }

        }

and in my context:

    public virtual DbSet<Product> Products { get; set; }

    public virtual DbSet<ProductPhotoMap> ProductPhotoMappings { get; set; }
8
  • Why the ToList()? Have you tried without it? Commented Jan 24, 2019 at 13:56
  • @haim770 Just gave it a whirl and get a new error: System.InvalidCastException: Unable to cast object of type 'SelectEnumerableAsyncIterator2[Microsoft.EntityFrameworkCore.Storage.ValueBuffer,System.String]' to type 'System.Linq.IQueryable1[System.String]'. Commented Jan 24, 2019 at 13:59
  • Where is your Product Image Model? Is ProductPhotoDto that model? Commented Jan 24, 2019 at 14:30
  • 1
    Please include your Product and ProductImage model classes to the question then it will be easy to answer. Commented Jan 24, 2019 at 14:31
  • 1
    The issue, as usual, is in your repo, which frankly looks broken in general. GetAll() should not be atomic if you want to support further querying. Regardless, we'll need to see the code for that to spot the problem. Commented Jan 24, 2019 at 14:49

2 Answers 2

1

.ToListAsync() does not return a List<string>. You can either update the property, or use .ToList() non asynchronously.

Sign up to request clarification or add additional context in comments.

2 Comments

It did work if I did make it var products = query.ToList() .. is there a way to do this asynchronously or is this the only option?
You realize that the list is already materialized in memory right? There's essentially no benefit for attempting to do this asynchronously.
1

Your whole query is in complete disarray. So its hard to guess what you are actually wanting. But I assumed you may be wanting something as follows:

So first update your Product class as follows:

public class Product
{
    public int Id { get; set; } //Aka productId

    public string Name { get; set; }

    public long ProductNumber { get; set; }

    public decimal? Size { get; set; }

    public string SizeMeasurement { get; set; }

    public decimal? Pack { get; set; }


    public ICollection<ProductPhotoMap> ProductPhotoMaps {get; set;}
}

Then write the query as follows:

var query = _productRepository.GetAll().Select(p => new ProductDto
       {
        Id = p.Id,
        ProductNumber = p.ProductNumber,
        Name = p.Name,
        Size = p.Size,
        SizeMeasurement = p.SizeMeasurement,
        Pack = p.Pack,
        PhotoFileNames = p.ProductPhotoMaps.Select(pp => pp.FileName).ToList()
    });

If(!string.IsNullOrWhiteSpace(input.Filter))
{
   query.Where(p => p.Name.Contains(input.Filter))
}

var productList = await query.ToListAsync();

5 Comments

Trying this now. Will let you know if it works :) Thanks!
Oh! I forgot to project the query to ProductDto. Please use the latest version of the answer.
I added the collection as suggested but still receive the same error mentioned above when trying to perform this ToListAsync() but if I just make it var productList = query.ToList() it works just fine. Not sure I understand why that is though.
Oh! your method is missing async Task<List<ProductDto>>. Please replace return type List<ProductDto> with async Task<List<ProductDto>>.
@Drewskis Have you understood your mistake?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.