LINQ get last position of special char

Question

i have a simple LINQ Query (linq to entities via EF)

var QueueList = (from q in context.queues                                        
                 select new { 
                             Qid = q.id, 
                             Qname = q.name.Substring(q.name.IndexOf(":") + 2) 
                 }).ToList();

The problem i have is the Qname. The name in the Database normally looks like this:

Company::QueueName

but now i have a few new Queues where the name looks like this:

Company::Group::QueueName

so i don't need to find the first ":" in the string but the last.

SQL doesn't support the "LastIndexOf()" method. So how do i get the last ":" in the QueueName?

I've tried it with "Reverse()" but the result was a little bit wrong (gnirts instead of string).

You can do `Reverse` twice, if that is working, but producing mirrored result. — Sinatr, Jan 26 '15 at 12:09
`q.name.Substring(q.name.IndexOf(":") + 2` works perfectly! within the query double reversing? — Dave Stockinger, Jan 26 '15 at 12:09
Why do you store multiple informations in a single string? Why don't you use multiple columns, probably new tables`Company`, `Group` and `Queue` and foreign-keys in the main table. — Tim Schmelter, Jan 26 '15 at 12:10
i don't store them. i'm still not allowed to touch the database (no questions about that please). So i have to do everything in my coding. like this small part. — Dave Stockinger, Jan 26 '15 at 12:11
an we store them like you said - Company, Group, Queue but unfortunately the QueueName looks like this. I have no influence on how the data are stored. — Dave Stockinger, Jan 26 '15 at 12:19

score 5 · Accepted Answer · answered Jan 26 '15 at 12:22

If I were you I'd give up trying to use functions that translate to Sql string functions for your string manipulation.

Instead just select the full string and translate it post retrieval.

ie:

var QueueList = (from q in context.queues                                        
                 select new { 
                             Qid = q.id, 
                             Qname = q.name 
                 }).ToList()
    .Select(x => new {
         Qid = x.Qid, 
         Qname = x.Qname.Substring(x.Qname.LastIndexOf(":") + 1) 
    });

moving the Substring/LastIndexOf functions to past the ToList() means they are just the .Net native functions acting on a string that has already been retrieved, rather than having to be translated to SQL functions acting on the database.

worked perfectly - i've tried it to write this anonymous `QueueList` into another or overwrite the Qname (which is not possible). But to select the Query itself was never in my mind - THANK YOU very much — Dave Stockinger, Jan 26 '15 at 12:29

LINQ get last position of special char

1 Answers1