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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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.

Gabriel Luci

Related Questions

In LINQ to Entities, concatenate int and string.

I am using the following code:...from c in Country where c.IsActive.Equals(true) orderby c.CountryName select new { countryIDCode = c.CountryID + "|" + c.TwoDigitCode, countryName = c.CountryName } ...But I get this error while running it:...Unable to cast the type 'System.Int32' to type 'System.Object'. LINQ to Entities only support...
entity-framework linq linq-to-entities
asked by Waheed

In LINQ to Entities, can I convert a string to an integer?

I have to convert a ...string... value to ...int..., but it seems LINQ to Entities does not support this....For the following code, I am getting an error....var query = (from p in dc.CustomerBranch where p.ID == Convert.ToInt32(id) // here is the error. select new Location() { Name = p.Br...
entity-framework linq-to-entities
asked by kandroid

From linq to entity, receive Dictionaryint, string>?

continuation of the issue ...How get array in linq to entity?...but now is not array => Dictionary City type is Dictionary... var sites = (from country in db.Countries select new { Country = country.Title, Cities = country.Cities.Select(m => m.Ti...
entity-framework linq-to-entities
asked by Mediator

This string is called Linq. Is there a more efficient approach to compare in the query?

Originally I had this method:...public virtual User GetUser(string username) { return _db.Users.Single(x => x.UserName == username); } ...So basically a linq method which gets a user based on the person's username....Trouble was I found that sometimes the username was coming through with an uppercase character at the sta...
entity-framework linq
asked by AnonyMouse

Entity Framework options to map list of strings or list of int (List<string>)

I want to store an object that contains a List of primitives using EF....public class MyObject { public int Id {get;set;} public virtual IList<int> Numbers {get;set;} } ...I know that ...EF cannot store... this, but I'd like to know possible solutions to solve this problem....The 2 Solutions I can think of are:...1....Create a Dummy object....
entity-framework list mapping orm
asked by Bernhard Kircher

Convert string to int in an Entity Framework linq query and handling the parsing exception

I've got this problem, I have a table for purchases ...Purchases(Date DateTime, Number string) ...I want is to create a new record, so I need the Max(Number), the problem here is that Number is a ...string..., I've tried ...Purchases.Select(X=>int.Parse(X.Number)).Max() ...but it could throw an exception, I've create a custom ...ToInt()... extensio...
c# entity-framework linq
asked by S3ddi9

linq to entities can not convert int to string

I try to filtering the data that return to kendo combo box, the filtering is based on the ID, I need to return all records that contains the filtration text not only the equals one, so what I did is to cast the ID to string as the following snip...Items = Mapper.Map<List<PurchaseOrder>, List<PurchaseOrderViewModel>>( purchaseOrderRe...
asp.net-mvc c# entity-framework linq
asked by Wael Joulani

convert int to string in linq for searching

I would like my users to be able to search for purchase orders (which are INT's) via partial strings, i.e. if a purchase order is 123456 typing in 456 gives me 123456 in the results....I thought this would work:... var pop = (from po in ctx.PurchaseOrders let poid = po.PurchaseOrderID.ToString() where poid.Conta...
asp.net c# entity-framework linq
asked by Gordon Copestake

The Entity Framework provider type 'System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer' could not be loaded

I created a WCF Service using dotNet 4.5. The database layer is built with Entity Framework 6....I hosted the service with IIS 8. It is working fine....Now I need to consume the service with windows forms client, that is built using dotnet framework 3.5. ...I can create the service reference successfully. But when I run the code, getting the follo...
.net entity-framework wcf winforms
asked by Ali Sakhi

Convert 'string' to 'int' in an .Any() LINQ query

I have a conditional statement that evaluates an ....Any()... LINQ query against my DB....It is throwing an error when casting/converting a string to long value....(long.TryParse(m.Reference,out t)? t : long.MaxValue) ...The error is something like:...LINQ to Entities does not recognize the method '....' method, and this method cannot be translated...
c# entity-framework entity-framework-6 linq sql-server-2012
asked by Mark

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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