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?
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
.
Thanks to Jeff Ogata with his answer!
var descriptions = ctx.MaterialDescriptions
Where(d => d.MaterialCode.Substring(SqlFunctions.PatIndex("%[^0]%", d.MaterialDescription) == material.MaterialCode.ToString()));
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);