Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
415 views
in Technique[技术] by (71.8m points)

c# - String.Split in a Linq-To-SQL Query?

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

1|12.6|18|19

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

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();

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.

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

This is .net 3.5SP1 if that matters.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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();

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...