Select query with subquery that can return List

asp.net-core entity-framework-core linq linq-to-entities

Question

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; }
1
0
1/24/2019 2:48:52 PM

Accepted Answer

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

1
1/24/2019 1:57:38 PM

Popular Answer

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();


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow