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

Question

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.

Answer 1

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

Answer 2

You can't use split, but in this scenario you can do the following:

// Database value is 1|12.6|18|19

string valueToFind = "19";

var temp = from r in db.SomeTable.Where(r => ("|" + r.MeterValues + "|").Contains("|" + valueToFind + "|"));

This code adds outer pipes (|) to the database value on the fly inside the query so you can do start, middle, and end value matches on the string.

For example, the above code looks for "|19|" inside "|1|12.6|18|19|", which is found and valid. This will work for any other valueToFind.