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.

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

Solution 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.

Solution 3

You don't need to use a temporary list:

var query = from r in db.SomeTable
            select new
            {
                r.Id,
                r.Name,
                r.MeterValues,
                ...
            };

var temp = from x in query.AsEnumerable()
           select new BusinessObject
           {
               Id = x.Id,
               Name = x.Name,
               MeterValues = x.mv.Split('|').Select(Decimal.Parse).ToArray(),
               ...
           };

return temp.ToArray();

Solution 4

Unfortunately its the IQueryable you are using (Linq to SQL) that is not supporting the Split function.

You are really only left with the IEnumerable (Linq to Objects) support for it in this case. You second code snippet is what you need to do, or something like...

var temp = (from r in db.SomeTable select new { 
    mv = r.MeterValues, 
    bo = new BusinessObject { // all the other fields } 
}).AsEnumerable().Select(blah, blah) ;

View more solutions