How can I get items that their names start with a number in Linq To Sql?

Question

How can I get a list of Products that, for example, theirs productName starts with a number?

Thanks!!

Answer 1

One way to do it:

Products.Where(n => n.ProductName.SubString(0,1) >= "0" && n.ProductName.SubString(0,1) <= "9")

Answer 2

List<string> digits = Enumerable.Range(0, 10).Select(i => i.ToString()).ToList();
var query = from p in db.Products
            where digits.Contains(p.productName.Substring(0, 1))
            select p;

Alternatively, add the following to your DBML mapping file:

<Function Name="ISNUMERIC" IsComposable="true">
    <Parameter Name="Expression" Parameter="Expression" Type="System.String" DbType="NVarChar(4000)" />
    <Return Type="System.Boolean" DbType="BIT NOT NULL"/>
</Function>

Then you can say:

var query = from p in db.Products
            where db.ISNUMERIC(p.productName.Substring(0, 1))
            select p;

You can do this in an XML mapping file too but I can't remember the syntax off the top of my head for the necessary code in your manually defined DataContext. Let me know if you need this.

I strongly prefer the first method but can see using the SQL built-in ISNUMERIC if profiling told me the pure LINQ version is a performance bottleneck.