联接表上的参数所在的Linq实体框架

Question

我试图在联接表的linq查询中添加where参数时遇到了问题，我在products表的ProductsListViewModel中显示了一个产品列表，需要通过ProductFilters表中的FilterId参数进行筛选，SKU是链接字段，但无法将FilterId参数添加到where子句。

以下是我迄今为止为ViewModel编写的代码：

public class ProductsListViewModel
{
    public IEnumerable<Product> Products { get; set; }
    public PagingInfo PagingInfo { get; set; }
    public string CurrentCategory { get; set; }
    public IEnumerable<ProductFilter> ProductFilters { get; set; }
}

控制器：

public ViewResult List(string category, string filtermaterial = null, string filterlength = null, string filtername = null, string filterid = null, int page = 1)
{ 
    ProductsListViewModel viewModel = new ProductsListViewModel
    { 
        Products = repository.Products
        .Where(p => category == null || p.Category == category)
        .Where(p => filtermaterial == null || p.FilterMaterial == filtermaterial)
        .Where(p => filterlength == null || p.FilterLength == filterlength)
        .OrderBy(p => p.ProductID)
        .Skip((page - 1) * PageSize)
        .Take(PageSize),
        PagingInfo = new PagingInfo
        {
            CurrentPage = page,
            ItemsPerPage = PageSize,
            TotalItems = category == null ?
                repository.Products.Count() :
                repository.Products.Where(e => e.Category == category).Count()
        },
        CurrentCategory = category
    };
    ViewBag.Category = category;
    return View(viewModel);
}

Product模型：

public class Product
{
    [HiddenInput(DisplayValue = false)]
    public int ProductID { get; set; }
    [Required(ErrorMessage = "Please enter a product name")]
    public string Name { get; set; }
    [Required(ErrorMessage = "Please enter the SKU for this product")]
    public string SKU { get; set; }
    public string MainImage { get; set; }
    [DataType(DataType.MultilineText)]
    [Required(ErrorMessage = "Please enter a description")]
    public string Description { get; set; }
    [DataType(DataType.MultilineText)]
    [Required(ErrorMessage = "Please enter product specification details")]
    public string Specification { get; set; }
    [Required]
    [Range(0.01, double.MaxValue, ErrorMessage = "Please enter a correct price")]
    public decimal Price { get; set; }
    [Required(ErrorMessage = "Please enter the Product Category")]
    public string Category { get; set; }
    //public int CategoryID { get; set; }
    [Required(ErrorMessage = "Please enter the Product Group")]
    public string ProductGroup { get; set; }
    [Required(ErrorMessage = "Please enter the search terms for this product")]
    public string SearchTerms { get; set; }
    public string FilterMaterial { get; set; }
    public string FilterLength { get; set; }
    public string Manufacturer { get; set; }
    public string Brand { get; set; }
    public string Details1 { get; set; }
    public string Details2 { get; set; }
    public string Details3 { get; set; }
    public string Details4 { get; set; }
    public string Spec1 { get; set; }
    public string Spec2 { get; set; }
    public string Spec3 { get; set; }
    public string Spec4 { get; set; }
    public virtual IQueryable<ProductFilter> ProductFilters { get; set; }
}

ProductFilter模型：

public class ProductFilter
{
    [Key]
    public int FilterId { get; set; }
    public string Filtername { get; set; }
    public string CategoryGroup { get; set; }
    public string SKU { get; set; }
}

带有加入的改进控制器

    public ViewResult List(string category, string filtermaterial = null, string filterlength = null, string filtername = null, string filterId = null, int page = 1)
    {         
        ProductsListViewModel viewModel = new ProductsListViewModel
        { 
            Products = repository.Products
            .Join(repository.ProductFilters, prod => prod.SKU, prodfilt => prodfilt.SKU, (prod, prodfilt) => new { Prod = prod, Prodfilt = prodfilt})
            .Where(p => category == null || p.Prod.Category == category)
            .Where(p => filtermaterial == null || p.Prod.FilterMaterial == filtermaterial)
            .Where(p => filterlength == null || p.Prod.FilterLength == filterlength)
            .Where(f => filterId == null || f.Prodfilt.FilterId == filterId)
            .OrderBy(p => p.Prod.ProductID)
            .Skip((page - 1) * PageSize)
            .Take(PageSize),
            PagingInfo = new PagingInfo
            {
                CurrentPage = page,
                ItemsPerPage = PageSize,
                TotalItems = category == null ?
                    repository.Products.Count() :
                    repository.Products.Where(e => e.Category == category).Count()
            },
            CurrentCategory = category
        };
        ViewBag.Category = category;
        return View(viewModel);
    }

Answer 1

'System.Linq.IQueryable<AnonymousType#1> to System.Collections.Generic.IEnumerable<WebStore.Domain.Entities.Product>

您的Viewmodel需要IEnumerable<Product>类型的属性。通过将Products与Productfilters连接，返回的实体的类型是匿名的。

要解决此冲突，您需要添加一个强类型的select语句：

Products = repository.Products
        .Join(repository.ProductFilters, prod => prod.SKU, prodfilt => prodfilt.SKU, (prod, prodfilt) => new { Prod = prod, Prodfilt = prodfilt})
        // ...
        .Select( x => new Product { ... }) //add this and clarify the properties.
        .Skip((page - 1) * PageSize)
        .Take(PageSize)

或者您更改ViewModel

public class ProductsListViewModel
{
    public IEnumerable<ProductAndFilterVM> FilterProducts { get; set; }
    public PagingInfo PagingInfo { get; set; }
    public string CurrentCategory { get; set; }
    public IEnumerable<ProductFilter> ProductFilters { get; set; }
}

这将需要您创建另一个ViewModel，它将表示已联接的实体。但你仍然需要选择。