CODEWITHSUNDEEP
c#sqllinqentity-frameworklinq-to-entities
Cody
Cody

Reputation: 8944

Select Max, but only on numeric values with LINQ to Entities

I am trying to select the max sys_id from a table where the sys_id column is a varchar type. Some rows contain text, but I am only concerned with the numeric values.

I have tried the following bit of code, but they do not work -- LINQ to Entities does not seem to support this operation.

public string GetSysID()
{
    using (var context = new DbEntities())
    {
        int i;
   //     var intQuery = context.myTable.Where(t => int.TryParse(t.sys_id, out i)).Max();

        int intQuery = Convert.ToInt32(context.myTable.Where(p => IsNumber(p.sys_id)).Max(p => p.sys_id));

        //return context.drawings.Max(p => p.sys_id);
        return intQuery.ToString();
    }
}

public static bool IsNumber(string value)
{
    int n;
    return int.TryParse(value, out n);
}

Is there a way to do this using LINQ to Entities?

Upvotes: 3

Views: 6605

Answers (5)

Saito
Saito

Reputation: 704

Use SqlFunctions.IsNumeric and Cast

iQueryableSource
.Where(x => SqlFunctions.IsNumeric(x.Number + ".0e0") == 1) // see explanation about .0e0 below
.Select(x => x.Number)
.Cast<int>()
.Max()

UPDATE

You can discover problem related to having a point or a currency sign in a string, but still getting IsNumeric == 1. Cause you want only integers, you should add a .0e0 to the string like this SqlFunctions.IsNumeric(x.Number + ".0e0") == 1

See this question and this blog post for details

Upvotes: 6

Tinu
Tinu

Reputation: 197

You are getting exception because the Linq-Entities cannot convert the IsNumber() methods to sql command

You can try this

using (var context = new DbEntities())
{    
    //First get all the sysIds
    var sysIds = (from tab in context.myTable
                  select tab.sys_id).ToList();              
    //Then use the custom method
    int intQuery = Convert.ToInt32(sysIds.Where(p => IsNumber(p.sys_id)).Max(p => p.sys_id));    

    return intQuery.ToString();
}

Upvotes: -1

Adam Valpied
Adam Valpied

Reputation: 178

Not that I am aware of. If it is not a large table then you could return all results by doing the following:

int intQuery = Convert.ToInt32(context.myTable.ToList().Where(p => IsNumber(p.sys_id)).Max(p => int.Parse(p.sys_id)));

Note that I've added int.Parse in the Max method - otherwise as @evanmcdonnal noted, a value of 2 would be higher than 10 as it would be a string comparison.

However using my example is not very efficient for a larger table. I find LinqToEntities not really suitable for this purpose. Maybe try creating a stored procedure that you can call on your db context, or use a view or something. That way you can make use of SQL specific functions and that will be much faster.

Upvotes: 0

evanmcdonnal
evanmcdonnal

Reputation: 48086

Max(p => p.sys_id)); this isn't going to behave how you expect it to...

You're expecting to get the results that the following would produce;

public static int Max(
    this IEnumerable<int> source
)

but in reality you're invoking the overload for IEnumerable<string>. As you're probably aware "2" > "10" evaluates to true. That might explain your results.

Basically just do Where(p => IsNumber(p)).Cast<int>().Max(); and you'll get what you're looking for.

Upvotes: -1

Selman Gen&#231;
Selman Gen&#231;

Reputation: 101681

Try this one: 

context.myTable
 .Where(c => c.sys_id.All(char.IsDigit))
 .Max(c => int.Parse(c.sys_id));

If it doesn't work try Contains like this:

 var numbers = new [] { '0','1','2','3','4','5','6','7','8','9' };
 var value = context.myTable
  .Where(c => c.sys_id.All(numbers.Contains))
  .Max(c => int.Parse(c.sys_id));

Upvotes: 1

Related Questions