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.

SLaks · Accepted Answer · 2010-11-29 00:32:30Z

6

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

answered Nov 29, 2010 at 0:32

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Michael Stum Over a year ago

Does that select the entire SomeTable Table though?

SLaks Over a year ago

@Michael: Yes. You need to select the entire table anyway. If you only need a subset of the fields, you can put an initial Select before the .AsEnumerable.

RPM1984 Over a year ago

Yep, this is not ideal - it will basically execute a "SELECT * FROM SomeTable". Does L2SQL support UDF's? EF does (i think). If so, you could do that split server-side (e.g UDF)

Michael Stum Over a year ago

@SLaks Sorry, I think my example was not too good because I omitted the where clause that I may have.

SLaks Over a year ago

@Michael: Put the where clause before AsEnumerable.

|

mhapps · Accepted Answer · 2017-01-23 13:29:47Z

4

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.

answered Jan 23, 2017 at 13:29

mhapps

1,18310 silver badges18 bronze badges

Comments

Thomas Levesque · Accepted Answer · 2010-11-29 00:35:11Z

2

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

answered Nov 29, 2010 at 0:35

Thomas Levesque

294k73 gold badges639 silver badges769 bronze badges

Comments

Tim Jarvis · Accepted Answer · 2010-11-29 00:33:17Z

1

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

answered Nov 29, 2010 at 0:33

Tim Jarvis

18.9k11 gold badges62 silver badges95 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related