Linq-To-SQL 查询中的 String.Split?

问题描述

我有一个包含 nvarchar 列的数据库表，如下所示:

I have a database table that contains an nvarchar column like this:

1|12.6|18|19

我有一个具有 Decimal[] 属性的业务对象.

I have a Business Object that has a Decimal[] property.

我的 LINQ 查询如下所示:

My LINQ Query looks like this:

var temp = from r in db.SomeTable select new BusinessObject {
    // Other BusinessObject Properties snipped as they are straight 1:1
    MeterValues = r.MeterValues.Split('|').Select(Decimal.Parse).ToArray()
};
var result = temp.ToArray();

这会引发 NotSupportedException:Method 'System.String[] Split(Char[])' 不支持转换为 SQL.

This throws an NotSupportedException: Method 'System.String[] Split(Char[])' has no supported translation to SQL.

这有点糟糕:) 有什么方法可以做到这一点，而无需向业务对象添加字符串属性或选择匿名类型然后遍历它?

That kinda sucks :) Is there any way I can do this without having to add a string property to the business object or selecting an anonymous type and then iterating through it?

我目前的解决方案"是:

My current "solution" is:

var temp = from r in db.SomeTable select new {
    mv = r.MeterValues,
    bo = new BusinessObject { // all the other fields }
};
var result = new List<BusinessObject>();
foreach(var t in temp) {
    var bo = t.bo;
    bo.MeterValues = t.mv.Split('|').Select(Decimal.Parse).ToArray();
    result.Add(bo);
}
return result.ToArray(); // The Method returns BusinessObject[]

不过，这个临时列表有点难看.

That's kinda ugly though, with that temporary list.

我尝试添加一个 let mv = r.MeterValues.Split('|').Select(Decimal.Parse).ToArray() 但这基本上会导致相同的 NotSupportedException.

I've tried adding a let mv = r.MeterValues.Split('|').Select(Decimal.Parse).ToArray() but that essentially leads to the same NotSupportedException.

如果重要的话，这是 .net 3.5SP1.

This is .net 3.5SP1 if that matters.

推荐答案

您需要先调用 .AsEnumerable() 来强制 select 子句在客户端上运行:

You need to force the select clause to run on the client by calling .AsEnumerable() first:

var result = db.SomeTable.AsEnumerable().Select(r => new BusinessObject {
    ...
    MeterValues = r.MeterValues.Split('|').Select(Decimal.Parse).ToArray()
}).ToList();