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

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

Question

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 into a store expression

Am I doing something wrong here? How can I achieve this?

using (var ctx = new DatabaseEntities())
{
    long t;
    if(!ctx.CustomerInboxes.Any(m=>m.CustomerId == customerId
        && m.Reference == item.ShoppingCartWebId.ToString() 
        && m.SubjectId == HerdbookConstants.PendingCartMessage 
        && item.ShoppingCartWebId > (long.TryParse(m.Reference,out t)? t : long.MaxValue)))
    )){
        // do something special
    }
}
1
1
8/8/2019 9:55:04 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

According to your code you when m.Reference is not a valid number the condition should fail, this can be done using SqlFunctions.IsNumeric().

To compare numbers you can use string.Compare and simulate numeric comparison padding left with 0s (it's possible with SqlFunctions.Replicate()).

It's not too pretty, but should works:

var itemId = item.ShoppingCartWebId.ToString();

ctx.CustomerInboxes.Any(m => ...
                          && SqlFunctions.IsNumeric(m.Reference) != 0 
                          && string.Compare(SqlFunctions.Replicate("0", m.Reference.Length > itemId.Length ? m.Reference.Length - itemId.Length : 0) + itemId, m.Reference) > 0);

However you always can switch to Linq to Objects to check this specific part:

ctx.CustomerInboxes.Where(m => m.CustomerId == customerId &&
                               m.Reference == item.ShoppingCartWebId.ToString()  &&
                               m.SubjectId == HerdbookConstants.PendingCartMessage)
                    .AsEnumerable()
                    .Any(c => item.ShoppingCartWebId > (long.TryParse(c.Reference, out t) ? t : long.MaxValue))
1
4/28/2016 7:28:39 PM

Popular Answer

using (var ctx = new DatabaseEntities())
{
    long t;
    if(!ctx.CustomerInboxes.ToList().Any(m=>m.CustomerId == customerId
        && m.Reference == item.ShoppingCartWebId.ToString() 
        && m.SubjectId == HerdbookConstants.PendingCartMessage 
        && item.ShoppingCartWebId > (long.TryParse(m.Reference,out t)? t : long.MaxValue)))
    ))
    {
        // do something special
    }
}

Just add .ToList(). While this will bring back your entire list to the client it will allow you to do the custom operators you want to do.

Based on the comments here is another alternative of bringing back a subset from the db then doing the custom parsing operator.

long t;
var initialQuery = ctx.CustomerInboxes.Where(x => m.CustomerId == customerId
    && m.Reference == item.ShoppingCartWebId.ToString()
    && m.SubjectId == HerdbookConstants.PendingCartMessage).ToList();

if (!initialQuery.Any(m => item.ShoppingCartWebId > (long.TryParse(m.Reference, out t) ? t : long.MaxValue)))
{
    // do something special
}
IdahoSixString

Related Questions

How i can combine these Linq query and how improve query speed

Try this one hope it solve your problem ... public async Task<List<GetAllOrdersResult>> AdminGetOrderList() { using (var db = new SoulDrawContext()) { var query = from A in db.Orders group A by A.OrderId into g join B in db.OrderMeta ...
c# entity-framework-core linq
asked by Kiran Joshi

.NET Core 2.x Identity int foreign key cannot target int primary key

The exception message is not quite clear, but usually indicates improper model configuration....There are several factors to be considered here....First, in version 2.0 the navigation properties have been removed from identity model, and the base ...IndentityDbCOntext... implementation ...explicitly... configures the relationships ...with no naviga...
asp.net-core asp.net-core-identity c# entity-framework-core invalidoperationexception
asked by Ivan Stoev

Where Clause is not applied when converting the query from LINQ (EF) to SQL

Here is how I fixed the issue: First of all, As per the suggestions above, I’ve removed the containsTest method and written everything in same method. ... var predicate = PredicateBuilder.True<StudentRbcasesLink>(); if (options.Contains("FirstName")) { firstName = list["FirstName"].ToLower().ToString(); Expression<Func<StudentRBbasesLink,...
automapper entity-framework-core linq sql sql-server-profiler
asked by LOKI

Searching for multiple strings using single database query with entity framework and LINQ

Alternative solution: use the EF 6.2 ...Like...:....Where(x => stringsToSearchFor.Any(s => DbFunctions.Like(x.name, $"%{s}%"))) ...Here's the documentation for ...DbFunctions.Like....
c# entity-framework entity-framework-6 linq linq-to-sql
asked by V0ldek

How to refactor Linq Group By and Foreach statement into one Linq Query

Without the actual database, I am not sure if this will work, but here is my attempt:...var qryResults = (from r in dbContext.Routes join br in dbContext.BrokerRoutes on r.RouteId equals br.RouteId where br.Broker.ApiKey == apiKey join pr in dbContext.RoutePathFilters.Include(x => x.PathFilter) on r.RouteId equals pr.RouteId into...
c# entity-framework-core linq linq-to-entities
asked by NetMage

FromSql(): Query string building from string list to prevent injection

There is few options:...Pass your values in XML (or JSON if using newer SQL Server) and then write static XML/JSON query....create a temporary table, insert all search values in a temp table and then execute static query.
.net-core c# entity-framework-core sql-injection sql-server
asked by Piotr

Compare int and string with leading zeros with LINQ and SQLServer

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...
c# entity-framework entity-framework-6 linq sql-server
asked by Gabriel Luci

Convert Enum From String To Int In DB Then Back To String - Entity Framework Core 3

This will return the enum string:...enum E : int { Element1 = 42 } //... int i = 42; string s = ((E)i).ToString();// returns "Element1" //...
asp.net-core-webapi c# entity-framework entity-framework-core enums
asked by beothunder

How to convert string to DateTime in C# EF Core query

If you ...really... can't change the underlying column type, then instead of unsupported ...Convert.ToDateTime... use C# cast operator which maps to PostgreSQL ...CAST... operator:...(DateTime)(object)s.TextDate >= DateTime.Now ...Note that the "intermediate" cast to ...object... is needed just to make the C# compiler happy....P.S. I really have no...
ef-core-3.0 entity-framework entity-framework-core linq postgresql-9.1
asked by Ivan Stoev

Linq inner query in join with entity framework, query error

You are left join results from a inner join. I am thinking that is missed in your c# code. But that is not the case for exception....you may try like this....var result = from ca in _dbcontext.CustomerAircrafts //// here join the inner join join pta in (from f in _dbcontext.AircraftPrices join pt in _dbcontext.PricingTemplate on ap.Pri...
entity-framework entity-framework-core linq linq-to-entities linq-to-sql
asked by cdev

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