“LINQ to Entities 无法识别方法“Int32 Parse(System.String)”的解决方法

发布于 2024-12-10 06:36:13 字数 2615 浏览 1 评论 0原文

我有一个看起来像这样的表:

Foo
 FooId : int (PK)
 BarId : int
 Baz   : bit
 etc.

它有其他列(等等),但我有一个特定的查询,我想运行该查询来项目一些统计数据。 SQL 中的查询如下所示:

SELECT 
 BarId, 
 SUM(CAST(Baz AS INT)) AS BazCount
 FROM Foo GROUP BY BarId;

因此,我创建了一个表示模型类来保存数据,以便可以将其返回给客户端。

public partial class FooStatistics
{
  public int BarId { get; set; }
  public int BazCount { get; set; }
}

我不是 100% 确定如何在 LINQ 中执行相同的查询并将其投影到此对象中,但我试了一下:

FooStatistics stats = (
    from f in ctx.Foo
    where <clauses here>
    group f by f.BarId
      into StatsGroup
      select new FooStatistics() {
        BarId = StatsGroup.Key,
        BazCount = StatsGroup.Sum(f => Int32.Parse(f.Baz.ToString()))
      }
    ).FirstOrDefault();

这会因错误而爆炸:

LINQ to Entities 无法识别方法 ' Int32 Parse(System.String)' 方法,并且该方法无法转换为存储表达式。

因此,我遵循了此处给出的建议:

LINQ to Entities 无法识别方法“Double” Parse(System.String)' 方法,并且此方法无法转换为存储表达式

我将其添加到我的 .edmx XML

<Function Name="ParseInt" ReturnType="Edm.Int32">
  <Parameter Name="value" Type="Edm.String" />
  <DefiningExpression>
     cast(value as Edm.Int32)
  </DefiningExpression>
</Function>

然后我添加了一个部分类来定义该方法:

public partial class MyEntities
{
   [EdmFunction("MyEntities", "ParseInt")]
   public static Int32 ParseInt(string value)
   {
       return Int32.Parse(value);
   }
}

并且我更改了我的 LINQ to:

FooStatistics stats = (
    from f in ctx.Foo
    where <clauses here>
    group f by f.BarId
      into StatsGroup
      select new FooStatistics() {
        BarId = StatsGroup.Key,
        BazCount = StatsGroup.Sum(f => MyEntities.ParseInt(f.Baz.ToString()))
      }
    ).FirstOrDefault();

但这会崩溃:

LINQ to Entities 无法识别方法“System.String ToString()”方法,并且此方法无法转换为存储表达式。

因此,我更改了.edmx XML 中的函数如下:

<Function Name="BoolToInt32" ReturnType="Edm.Int32">
   <Parameter Name="value" Type="Edm.Boolean" />
   <DefiningExpression>
      cast(value as Edm.Int32)
   </DefiningExpression>
</Function>

我相应地将静态 ParseInt 更改为 BoolToInt32,并更改了 LINQ 以使用该函数,但现在它爆炸了:

指定的方法'Int32 BoolToInt32(Boolean)' 无法转换为 LINQ to Entities 存储表达式。

我是接近了,还是我做的完全错误......?

提前致谢

I've got a table that looks like this:

Foo
 FooId : int (PK)
 BarId : int
 Baz   : bit
 etc.

It has other columns in it (etc.), but I have a specific query that I want to run that projects some statistics. The query in SQL would look like this:

SELECT 
 BarId, 
 SUM(CAST(Baz AS INT)) AS BazCount
 FROM Foo GROUP BY BarId;

So, I created a Presentation Model class to hold the data, so I can return it to the client.

public partial class FooStatistics
{
  public int BarId { get; set; }
  public int BazCount { get; set; }
}

I'm not 100% sure how to do the same query in LINQ and project it into this object, but I gave it a shot like this:

FooStatistics stats = (
    from f in ctx.Foo
    where <clauses here>
    group f by f.BarId
      into StatsGroup
      select new FooStatistics() {
        BarId = StatsGroup.Key,
        BazCount = StatsGroup.Sum(f => Int32.Parse(f.Baz.ToString()))
      }
    ).FirstOrDefault();

This blows up with the error:

LINQ to Entities does not recognize the method 'Int32 Parse(System.String)' method, and this method cannot be translated into a store expression.

So, I followed the advice given here:

LINQ to Entities does not recognize the method 'Double Parse(System.String)' method, and this method cannot be translated into a store expression

I added this to my .edmx XML

<Function Name="ParseInt" ReturnType="Edm.Int32">
  <Parameter Name="value" Type="Edm.String" />
  <DefiningExpression>
     cast(value as Edm.Int32)
  </DefiningExpression>
</Function>

Then I added a partial class to define the method:

public partial class MyEntities
{
   [EdmFunction("MyEntities", "ParseInt")]
   public static Int32 ParseInt(string value)
   {
       return Int32.Parse(value);
   }
}

And I changed my LINQ to:

FooStatistics stats = (
    from f in ctx.Foo
    where <clauses here>
    group f by f.BarId
      into StatsGroup
      select new FooStatistics() {
        BarId = StatsGroup.Key,
        BazCount = StatsGroup.Sum(f => MyEntities.ParseInt(f.Baz.ToString()))
      }
    ).FirstOrDefault();

But this blows up with:

LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.

So, I changed the function in the .edmx XML to this:

<Function Name="BoolToInt32" ReturnType="Edm.Int32">
   <Parameter Name="value" Type="Edm.Boolean" />
   <DefiningExpression>
      cast(value as Edm.Int32)
   </DefiningExpression>
</Function>

And I changed my static ParseInt to BoolToInt32 accordingly and I changed the LINQ to use that function, but now it blows up with:

The specified method 'Int32 BoolToInt32(Boolean)' cannot be translated into a LINQ to Entities store expression.

Am I close, or am I doing it totally wrong...?

Thanks in advance

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

迷雾森÷林ヴ 2024-12-17 06:36:13

如果 Baz 有点,你可以添加一个 where 然后只需使用 Count() 如下

FooStatistics stats = (
    from f in ctx.Foo
    where <clauses here>
       and f.Baz
    group f by f.BarId
      into StatsGroup
      select new FooStatistics() {
        BarId = StatsGroup.Key,
        BazCount = StatsGroup.Count()
      }
    ).FirstOrDefault();

If Baz is a bit, you could add a where and then just use Count() as below

FooStatistics stats = (
    from f in ctx.Foo
    where <clauses here>
       and f.Baz
    group f by f.BarId
      into StatsGroup
      select new FooStatistics() {
        BarId = StatsGroup.Key,
        BazCount = StatsGroup.Count()
      }
    ).FirstOrDefault();
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文