IsNumeric check in linq

Question

The following linq

var subjectMarks = (from DataRow row in objDatatable.Rows
                    select Convert.ToDecimal(row["EXM_MARKS"])).Sum();

throws an exception since some row["EXM_MARKS"] has non numeric values like AB etc. How can I get the sum of only numeric ones out of them?

score 9 · Accepted Answer · answered Sep 28 '11 at 10:07

9

Add where clause that filters out the records that cannot be parsed as decimals. Try:

decimal dummy;

var subjectMarks = (from DataRow row in objDatatable.Rows
                     where decimal.TryParse(row["EXM_MARKS"], out dummy)
                     select Convert.ToDecimal(row["EXM_MARKS"])).Sum();

answered Sep 28 '11 at 10:07

Jakub Konecki

45,581
7
87
126

I knw that , I am asking is ther any direct function to handle that ? – Kuntady Nithesh Sep 28 '11 at 10:08
You can select the dummy value without additional Convert.ToDecimal call – Yuriy Rozhovetskiy Sep 28 '11 at 10:16
1

@YuriyRozhovetskiy: No, you can't. This would select the value of the last row for all rows. – Daniel Hilgarth Sep 28 '11 at 10:30
if so, how you can explain this: string value = "1 a 2 b 3 c"; int dummy = 0; var digits = (from digit in value.Split(' ') where int.TryParse(digit, out dummy) select dummy).ToList(); digits.ForEach(Console.WriteLine); – Yuriy Rozhovetskiy Sep 28 '11 at 10:46
@YuriyRozhovetskiy: Yes, you are right. The deferred execution got me. – Daniel Hilgarth Sep 28 '11 at 11:20

score 5 · Answer 2 · edited Aug 11 '17 at 03:43

You could create an extension method SafeConvertToDecimal and use this in your LINQ query:

var subjectMarks = (from DataRow row in objDatatable.Rows
                    select row["EXM_MARKS"].SafeConvertToDecimal()).Sum();

This extension method would look like this:

public static decimal SafeConvertToDecimal(this object obj)
{
    decimal result;
    if(!decimal.TryParse(obj.ToString(), out result))
        result = 0;

    return result;
}

This approach has the advantage that it looks clean and you aren't converting each object twice as do all other answers.

score 2 · Answer 3 · answered Nov 14 '18 at 07:56

2

For linq to sql queries you can use built-in SqlFunctions.IsNumeric

Source: SqlFunctions.IsNumeric

answered Nov 14 '18 at 07:56

irfandar

1,690
1
23
24

A. Morel · Answer 4 · 2022-07-29T16:27:30.730

For LINQ to SQL queries with Entity Framework Core, ISNUMERIC function is part of EF Core 6.0 under EF.Functions

Before version 6.0, you can create a user-defined function mapping !

User-defined function mapping : Apart from mappings provided by EF Core providers, users can also define custom mapping. A user-defined mapping extends the query translation according to the user needs. This functionality is useful when there are user-defined functions in the database, which the user wants to invoke from their LINQ query for example.

await using var ctx = new BlogContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

_ = await ctx.Blogs.Where(b => ctx.IsNumeric(b.Name)).ToListAsync();

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0")
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDbFunction(IsNumericMethodInfo)
            .HasName("ISNUMERIC")
            .IsBuiltIn();
    }

    private static readonly MethodInfo IsNumericMethodInfo = typeof(BlogContext)
        .GetRuntimeMethod(nameof(IsNumeric), new[] { typeof(string) });

    public bool IsNumeric(string s) => throw new NotSupportedException();
}

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Please avoid the phrase "LINQ to SQL" in an Entity Framework context. It's quite a different thing. — Gert Arnold, Jul 29 '22 at 17:54
It's worth noting that the built-in `ISNUMERIC` will return true for some non-numeric characters, e.g., "+20", "-30", "$2.00". But for expressions like "20+" it will return false, so character positioning matters. Bottom line: If you're looking strictly to exclude all non-numeric characters, you'll need to look elsewhere. — Tawab Wakil, Aug 03 '23 at 21:49

score 1 · Answer 5 · edited Sep 28 '11 at 10:12

1

use

Decimal Z;

var subjectMarks = (from DataRow row in objDatatable.Rows
                     where Decimal.TryParse (row["EXM_MARKS"], out Z)
                     select Convert.ToDecimal(row["EXM_MARKS"])).Sum();

edited Sep 28 '11 at 10:12

VdesmedT

9,037
3
34
50

answered Sep 28 '11 at 10:11

Yahia

69,653
9
115
144

score 1 · Answer 6 · answered Sep 28 '11 at 10:17

You can make some extensions utility class like this for elegant solution:

public static class TypeExtensions
{
    public static bool IsValidDecimal(this string s)
    {
        decimal result;
        return Decimal.TryParse(s, out result);
    }
}

and use it in this way:

var subjectMarks = (from DataRow row in objDatatable.Rows
                     where row["EXM_MARKS"].IsValidDecimal()
                     select Convert.ToDecimal(row["EXM_MARKS"])).Sum();

Hope this helps.

score 0 · Answer 7 · answered Nov 11 '17 at 11:31

0

var subjectMarks = objDatatable.Rows.Where(row => row["EXM_MARKS"].ToString().All(char.isDigit))

part of solution is getted in: here

answered Nov 11 '17 at 11:31

Dave

7,028
11
35
58

result will be an array with all items numeric – Dave Nov 11 '17 at 11:32

IsNumeric check in linq

7 Answers7

Linked