必需的列“int_CommodityPriceId”结果中不存在。 LINQ 到 SQL

Question

public IEnumerable<CommodityPrice> GetAllTimeFrameDaily()
    {
        return _dbprice.ExecuteQuery<CommodityPrice>("SELECT MIN(CommodityPrice.dtm_Date),MAX(CommodityPrice.dtm_Date) FROM CommodityPrice WHERE (CommodityPrice.int_FrequencyId = 1) and int_MarketLocationId in (SELECT [int_LocationId] FROM [Location] where int_LocationTypeId=4  and int_ParentLocationId in (SELECT [int_LocationId] FROM [Location] where int_LocationTypeId=2 and int_ParentLocationId in (SELECT int_LocationId FROM [Location] where int_LocationTypeId = 1 and vcr_LocationEn='Pakistan')))");
    }

我收到此错误结果中不存在所需的列“int_CommodityPriceId”。知道为什么要这样做。 （我的 dbml 文件确实有 CommodityPriceId 列）。我正在使用 linq to sql 和 vs 2010.在 sql server 中的运行文件中运行它

commodityprice

int_CommodityPriceId bigint Unchecked
int_CommodityId bigint  Checked
int_SourcesId   int Checked
int_MarketLocationId    int Checked
int_FrequencyId int Checked
flt_Price   float   Checked
flt_PriceUSD    float   Checked

Answer 1

此代码尝试将结果分配给 CommodityPrice 实体。
为此，它需要返回 CommodityPrice 实体列属性中定义的所有列。您的查询不会返回其中任何列。

如果您只想返回这 2 个字段，您将需要一个仅包含 2 个日期字段的新实体..

TimeFrame
dte_CommodityPriceMinDate  datetime  Unchecked
dte_CommodityPriceMaxDate  datetime  Checked

并且查询应填充此字段：

public IEnumerable<TimeFrame> GetAllTimeFrameDaily()
    {
        return _dbprice.ExecuteQuery<TimeFrame>("SELECT MIN(CommodityPrice.dtm_Date) AS dte_CommodityPriceMinDate ,MAX(CommodityPrice.dtm_Date) AS dte_CommodityPriceMaxDate FROM CommodityPrice WHERE (CommodityPrice.int_FrequencyId = 1) and int_MarketLocationId in (SELECT [int_LocationId] FROM [Location] where int_LocationTypeId=4  and int_ParentLocationId in (SELECT [int_LocationId] FROM [Location] where int_LocationTypeId=2 and int_ParentLocationId in (SELECT int_LocationId FROM [Location] where int_LocationTypeId = 1 and vcr_LocationEn='Pakistan')))");
    }