Compare int and string with leading zeros with LINQ and SQLServer

c# entity-framework entity-framework-6 linq sql-server

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.

1
2
12/15/2019 2:31:57 PM

Accepted Answer

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()));
3
12/12/2019 7:41:50 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow