Linq查询导致无效的列名SqlException [英] Linq query leading to invalid column name SqlException

问题描述

ASPNET MVC5 Web应用程序，Visual Studio 2015

ASPNET MVC5 web app, Visual Studio 2015

该网站是语言本地化的，因此我需要根据文化参数获取本地化的类别.

The website is language localized, so I need to get localized Categories depending on culture parameter.

category_trans表用于提供类别名称转换，而ISO_Languages表用于接收区域性代码参数并将其相关ID传递给category_trans.

The category_trans table accounts for providing category name translations while the ISO_Languages accounts for receiving a culture code parameter and passing its relating id the category_trans.

型号:

public partial class Product
{
    public int? ID { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public int CategoryID { get; set; }
    public virtual category_trans category_trans { get; set; }
}

public partial class Category
{
    public int ID { get; set; }
    public string Name { get; set; }
    public int? ParentID { get; set; }
    public bool IsDeleted { get; set; }
}

public class category_trans
{
    [Key]
    [Column(Order = 1)]
    public int category_id { get; set; }
    [Key]
    [Column(Order = 2)]
    public int language_id { get; set; }
    public string name { get; set; }
}

public class ISO_Languages
{
    public int ID { get; set; }
    public string code { get; set; }
    public bool IsEnabled { get; set; }
    public string name_en { get; set; }
    public string name_fr { get; set; }
    public string name_it { get; set; }
    public string name_de { get; set; }
    public string name_es { get; set; }
}

public static IQueryable<Product> ActiveProductsPerUser(BaseContext db, string userid, string culture)
    {
        return from p in db.Products
               from ct in db.category_trans
               from l in db.ISO_Languages
               where (ct.category_id == p.CategoryID
               && ct.language_id == l.ID
               && l.code.Substring(0, 2) == culture)
               select p;
    }

导致错误:

System.Data.SqlClient.SqlException:无效的列名'category_trans_category_id'.无效的列名'category_trans_language_id'.在System.Data.SqlClient.SqlCommand中.<> c.b__174_0(Task 1 result)
in System.Threading.Tasks.ContinuationResultTaskFromResultTask 2.InnerInvoke()
在System.Threading.Tasks.Task.Execute()

System.Data.SqlClient.SqlException: Invalid column name 'category_trans_category_id'. Invalid column name 'category_trans_language_id'. in System.Data.SqlClient.SqlCommand.<>c.b__174_0(Task1 result)
in System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()
in System.Threading.Tasks.Task.Execute()

还请注意，我正在将预期结果传递给另一种排序和分页方法，因此接受IQueryable作为输入(没有IEnumerable).

Please also note I am passing the expected result to another method for sorting and paging, thus accepting an IQueryable as input (no IEnumerable).

推荐答案

尝试一下；

public static IQueryable<Product> ActiveProductsPerUser(BaseContext db, string userid, string culture)
    {
        var query = (from p in db.Products
                     join ct in db.category_trans
                     on p.CategoryID equals ct.category_id
                     join l in db.ISO_Languages
                     on ct.language_id equals l.ID
                     where l.code.Substring(0, 2) == culture)
                     select p).ToList();

        return query;
    }

这篇关于Linq查询导致无效的列名SqlException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！