EF Core 7实体属性映射DTO报错解决及实现方法

错误信息 #

Unable to cast object of type 'Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1[PrjOnlineStore.Core.Domain.Products.ProductDto]' to type 'System.Linq.IQueryable1[PrjOnlineStore.Core.Domain.Products.Entities.TblProducts]'.

原问题代码 #

[HttpGet("GetProductDetailsByProductID")]
public IActionResult GetProductDetailsByProductID(int? ProductID)
{
    if (ProductID == null)
    {
        return BadRequest();
    }

    var GetProduct = _Context.TblProducts.AsQueryable();

    var GetAllProductsVariant = _Context.TblProductVariants
                             .Where(x => x.RefProductID == ProductID)
                             .AsEnumerable();

    var GetAllProductDetails = _Context.TblProductDetails
                            .Where(x => x.RefProductID == ProductID);

    foreach (var item in GetAllProductsVariant)
        GetProduct = (IQueryable<TblProducts>)GetProduct
                             .Where(x => x.ProductID == ProductID &&
                                    x.ProductVariants.Any(y => 
                                        y.RefProductID == ProductID.Value))
                            .Select(x => new ProductDto {
                               ID = x.ProductID,
                               Name = x.Title,
                               Price = x.Price
                    }).Distinct();

    foreach (var item in GetAllProductDetails)
        GetProduct = GetProduct.Where(x => x.ProductID == ProductID
                                   && x.ProductDetails.Any(y => y.RefProductID == item.RefProductID));

    return Ok(GetProduct);
}

实体模型 #

[Table("TblProducts")]
public class TblProducts
{
    [Key]
    public int ProductID { get; set; }

    [Required]
    public string? Title { get; set; }

    public DateTime UploadDate { get; set; }

    public decimal Price { get; set; }

    public long Visit { get; set; }

    public bool IsAvaiable { get; set; }
    [JsonIgnore]
    [NotMapped]
    public bool IsSendImmediate { get; set; }
    [JsonIgnore]
    [NotMapped]
    public bool IsAvailableAtStore { get; set; }
    [JsonIgnore]
    [NotMapped]
    public string? ProductRate { get; set; }
    [JsonIgnore]
    public string? ProductColor { get; set; }
    [JsonIgnore]
    [NotMapped]
    public string? ProductImage { get; set; }
    [JsonIgnore]
    [NotMapped]
    public string? Description { get; set; }
    [JsonIgnore]
    [NotMapped]
    public string? Tags { get; set; }
    [JsonIgnore]
    [NotMapped]
    public bool IsOriginal { get; set; }
    [JsonIgnore]
    [Required]
    public string? ProductType { get; set; }

    public long CategoryID { get; set; }

    public long BrandID { get; set; }
    [JsonIgnore]
    [NotMapped]
    public virtual TblCategory Categorys { get; set; }
    [JsonIgnore]
    [NotMapped]
    public virtual TblBrands Brands { get; set; }

    [JsonIgnore]
    [ForeignKey("RefProductID")]
    public virtual ICollection<TblOrders>? Orders { get; set; }
    
    [ForeignKey("RefProductID")]
    public virtual ICollection<TblProductDetails> ProductDetails { get; set; }
    
    [ForeignKey("RefProductID")]
    public virtual ICollection<TblProductVariants> ProductVariants { get; set; }
}

错误原因 #

• 类型不匹配：GetProduct被声明为IQueryable<TblProducts>，但执行Select(x => new ProductDto)后返回的是IQueryable<ProductDto>，强制类型转换必然失败，这是报错的直接原因。
• 逻辑冗余错误：单独查询关联表后循环过滤完全没必要，EF Core可通过实体导航属性直接关联查询，循环重复修改查询会导致逻辑混乱。
• 混合内存与数据库操作：GetAllProductsVariant调用AsEnumerable()将数据加载到内存，无法利用数据库查询优化。

修正后的代码 #

[HttpGet("GetProductDetailsByProductID")]
public IActionResult GetProductDetailsByProductID(int? ProductID)
{
    if (ProductID == null)
    {
        return BadRequest();
    }

    // 通过导航属性关联查询，最后统一映射到DTO
    var productDto = _Context.TblProducts
        .Where(x => x.ProductID == ProductID.Value)
        // 提前加载关联数据，避免N+1查询
        .Include(x => x.ProductVariants)
        .Include(x => x.ProductDetails)
        // 过滤存在对应变体和详情的产品（按需保留）
        .Where(x => x.ProductVariants.Any(v => v.RefProductID == ProductID.Value) 
                    && x.ProductDetails.Any(d => d.RefProductID == ProductID.Value))
        .Select(x => new ProductDto 
        {
            ID = x.ProductID,
            Name = x.Title,
            Price = x.Price
            // 如需映射子属性，可继续添加：
            // Variants = x.ProductVariants.Select(v => new ProductVariantDto { ... }),
            // Details = x.ProductDetails.Select(d => new ProductDetailDto { ... })
        })
        .Distinct()
        .FirstOrDefault();

    return Ok(productDto);
}

关键说明 #

• DTO映射时机：始终在查询的最后一步做DTO转换，避免中途改变查询泛型类型，引发类型不匹配错误。
• 利用导航属性：EF Core导航属性已关联实体关系，无需单独查询关联表再循环过滤，直接通过Any、Include等方法完成关联查询。
• 避免不必要内存操作：非必要不要调用AsEnumerable()/ToList()，保持IQueryable让EF Core生成最优SQL在数据库端执行。
• 类型一致性：确保变量泛型类型与查询返回类型一致，不做无意义的强制类型转换。

内容的提问来源于stack exchange，提问作者Dariush Marandi