How to do string combining and querying with Linq

Question

How can I combine variables to a string and use the string in a where condition within a LINQ query? I have tried this:

query = query.Where(p => (p.Cod + p.Year + p.Count).StartsWith("BS201412"));

My variables are as follows:

Cod is of type string
Year is of type short
Count is of type int

I have also tried this:

  query = query.Where(p => (p.Cod + SqlFunctions.StringConvert((double) p.Year)+ SqlFunctions.StringConvert((double) p.Count)).StartsWith("BS201412"));

but because of Year the query is not working in both variants.

Are the 3 properties in your code the only ones in that class? If so, you may want to override ToString so that it looks something like query.Where(p => p.ToString().StartsWith("value")). Also is StartsWith actually what you want? What happens if Count = 123 or Count = 12? Should that be treated the same? — Darren Young
– Darren Young, Commented Mar 16, 2014 at 19:50

keenthinker · Accepted Answer · 2014-03-17 17:43:31Z

2

You have many options and two of them are:

use String.Format - it converts automatically every parameter to string
use the ToString method on the complex variable

Example:

string cod = "BS";
short year = 14;
int count = 100;
Console.WriteLine(String.Format("{0}{1}{2}", cod, year, count));
Console.WriteLine(cod + year.ToString() + count.ToString());

The output is in both cases the same:

BS14100
BS14100

Your query line could look like this:

query = query
        .Where(p => String.Format("{0}{1}{2}", p.Cod, p.Year, p.Count)
        .StartsWith("BS201412"));

Because you are using LINQ2SQL some of the functions can not be used (because they can not be translated to SQL directly). You can try a different approach - it looks like you have the String you want to search for BS201420, so you can partition the string, convert every part to the corresponding type and write a normal query, something like this:

var searchFor = "BS201420";
var cod = searchFor.Substring(0, 2);
// example - use short.TryParse in your code
var year = short.Parse(searchFor.Substring(2, 4));
// example - use int.TryParse in your code
var count = int.Parse(searchFor.Substring(4,2));
query = query.Where(p => p.Cod == cod && p.Year == year && p.Count == count);

This should find the same result set as with the string and starts with.

edited Mar 17, 2014 at 17:43

answered Mar 16, 2014 at 19:40

keenthinker

7,8402 gold badges38 silver badges47 bronze badges

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

7 Comments

MEMark Over a year ago

Just for readability, you might want to use query syntax with a let expression: query = from q in query let key = String.Format("{0}{1}{2}", p.Cod, p.Year, p.Count) where key.StartsWith("BS201412"); (Which you could also line-break if the comment field allowed it. :))

Selman Genç Over a year ago

I'm pretty sure this won't work: query = query.Where(p => p.Stringify().StartsWith("BS201412")); this is LINQ to SQL (or Entities) in either case you can't use a custom function within a query.

keenthinker Over a year ago

A partial class that adds the functionality to the Entity can be created.

keenthinker Over a year ago

Examples from comments where removed.

user3403034 Over a year ago

I have also tried this: <pre> query.Where(p => String.Format("{0}{1}{2}", p.Cod, p.Year, p.Count).StartsWith("BS14100")); </pre> this exception: LINQ to Entities does not recognize the method 'System.String Format(System.String, System.Object, System.Object, System.Object)' method, and this method cannot be translated into a store expression.

|

Collectives™ on Stack Overflow

How to do string combining and querying with Linq

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related