3

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?

Cody
  • 8,686
  • 18
  • 71
  • 126

5 Answers5

6

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

Community
  • 1
  • 1
Saito
  • 694
  • 7
  • 25
1

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));
Selman Genç
  • 100,147
  • 13
  • 119
  • 184
  • Those solutions will both work but not because you replaced the lambda expression in his `Where` with inferior code. It's because you added `int.Parse` in `Max`'s lambda expression. – evanmcdonnal Feb 17 '14 at 23:55
  • @evanmcdonnal No, `IsNumber` is a custom method and cannot be translated to `SQL`. – Selman Genç Feb 17 '14 at 23:57
  • Neither work. They both give me the error: `Internal .NET Framework Data Provider error 1025.` – Cody Feb 18 '14 at 00:13
  • 1
    @Selman22 yeah, you're right. I overlooked the fact that it's LINQ to EF. – evanmcdonnal Feb 18 '14 at 00:47
  • Based on this answer, this works for me. Convert.ToInt32(pl.Where(x=> x.PO_LineNumber.All(char.IsNumber)).Max(a => a == null ? "0" : a.PO_LineNumber)); – Randeep Singh Jun 21 '17 at 15:38
0

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.

Adam Valpied
  • 178
  • 6
  • Sorry - I should have said that it works because adding .ToList() makes LinqToEntities execute the query and return the entire table. Then you can use LinqToObjects to get the max ID. You could make it more efficient by just returning the sys_id field in your query before the .ToList() so only that field is returned, but it's still not a very efficient way of doing things. – Adam Valpied Feb 18 '14 at 00:28
-1

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.

evanmcdonnal
  • 46,131
  • 16
  • 104
  • 115
  • This doesn't seem to work in any way, the `IsNumber` is not allowed and even replacing it with the contents of the method - does not work. – Cody Feb 18 '14 at 00:10
  • @Cody oh yeah I overlooked that part of it. I suggest making it run as LINQ to Objects or make a sproc and stop trying to use LINQ to EF. I don't think you're going to be able to find the solution you want because the `IsNumber` check (or other C# options) not having SQL equivalents. Your `Max` function still will not work for the reasons I stated. – evanmcdonnal Feb 18 '14 at 00:45
  • There is a special SqlFunctions.IsNumeric method that is translated nicely into SQL by Linq (e.g. `.Where(p => SqlFunctions.IsNumeric(p.sys_id) == 1)`). The problem is that there is no way of converting a string to an int (though you can do the opposite) for performing the Max part of the expression. – Adam Valpied Feb 18 '14 at 07:57
  • @AdamValpied good to know. However it doesn't effect my advice, it seems LINQ to SQL is just too restrictive. Of course there are performance benefits of doing everything in SQL but I'd rather come up with ways to optimize the retrieval of data and operate on it in C# code. – evanmcdonnal Feb 18 '14 at 16:59
  • there is no IsNumber function – phil123456 Feb 18 '21 at 13:34
-1

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();
}
Tinu
  • 197
  • 2
  • 9