Compare int and string with leading zeros with LINQ and SQLServer

Question

Context

I have two entities in database with a one to many relationship that looks like this:

public class Material {
    public long MaterialCode { get; set; }
}

public class MaterialDescription {
   [StringLength(18, MinimumLength = 18)]
   public string MaterialCode { get; set; }
}

How can I use LINQ to query for all descriptions for a given material?

What I've tried?

    var descriptions = ctx.MaterialDescripions
        .Where(d => long.Parse(d.MaterialCode) == material.MaterialCode);

Gives error: LINQ to Entities does not recognize the method Int64 Parse(System.String).

    var descriptions = ctx.MaterialDescriptions
        .Where(d => d.MaterialCode.TrimStart('0') == material.MaterialCode.ToString());

Gives error: System.String TrimStart(char[]) is only supported in LINQ to Entities when there are no trim characters specified as arguments.

Have you tried `Where(d => d.MaterialCode == material.MaterialCode.ToString("D" + d.MaterialCode.Length))`? — Heretic Monkey, Dec 12 '19 at 19:30
Is the `MaterialDescription.MaterialCode` you're comparing against coming as an input to the method that queries the database? @HereticMonkey LINQ to SQL doesn't support `ToString` either. — Matt U, Dec 12 '19 at 19:31
@MattU Right, `StringFunctions.StringConvert(material.MaterialCode, d.MaterialCode.Length)`, from https://stackoverflow.com/q/1066760/215552 — Heretic Monkey, Dec 12 '19 at 19:38

score 4 · Accepted Answer · answered Dec 12 '19 at 19:41

Finally came with the solution!

Thanks to Jeff Ogata with his answer!

    var descriptions = ctx.MaterialDescriptions
        Where(d => d.MaterialCode.Substring(SqlFunctions.PatIndex("%[^0]%", d.MaterialDescription) == material.MaterialCode.ToString()));

score 2 · Answer 2 · answered Dec 12 '19 at 19:37

SQL Server (assuming that's what you're using) will let you cast a string with leading zeros to a bigint, so you could just construct the SQL query yourself:

var descriptions = ctx.MaterialDescriptions
    .SqlQuery("SELECT * FROM MaterialDescriptions WHERE CAST(MaterialCode AS bigint) = @p0",
              material.MaterialCode);

You can read more about raw SQL queries here and here.

Compare int and string with leading zeros with LINQ and SQLServer

Context

What I've tried?

2 Answers2

Finally came with the solution!