Filtering by parsed int, if string field was parseable to int (inside Linq to Entites)

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for SQLite
Post Reply
bairog
Posts: 120
Joined: Mon 29 Apr 2013 09:05

Filtering by parsed int, if string field was parseable to int (inside Linq to Entites)

Post by bairog » Fri 22 Jun 2018 06:40

Hello.
I want to select entites with Linq to Entites applying among others a filter by parsed int (if entitie's string feild was parseable to int):

Code: Select all

Int32 CodeAsInt = Int32.MinValue;
var result = MyDBContext.MyEntites.Where(e => (e.Name == "test") && 
(Int32.TryParse(e.StringCode, out CodeAsInt)) && (CodeAsInt == 333)).Tolist()
The code above fails because Linq to Entites doesn't support Int32.TryParse method.
The only workaround for now is to switch from Linq to Entites to Linq to Objects via .AsEnumerable() method:

Code: Select all

Int32 CodeAsInt = Int32.MinValue;
var result = MyDBContext.MyEntites.Where(e => (e.Name == "test")).AsEnumerable().
Where(e => (Int32.TryParse(e.StringCode, out CodeAsInt)) && (CodeAsInt == 333)).Tolist()
But that approach is ugly and slow (because .AsEnumerable() loads data from database to memory - and amount of that data is significantly more that I really need).

In EF provider for SQL Server (System.Data.Objects.SqlClient) there is special helper class SqlFunctions which among others contains IsNumeric method (which succesfully can be used inside Linq to Entites).

Is there some similar function in Devart DotConnect for SLite?
If no - is there some other workaround, without using .AsEnumerable()?
Last edited by bairog on Thu 23 Aug 2018 05:23, edited 1 time in total.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Filtering by parsed int, if string field was parseable to int (inside Linq to Entites)

Post by Shalex » Mon 25 Jun 2018 17:24

EF6: the Devart.Data.SQLite.Entity.SQLiteFunctions class is implemented but there is no IsNumeric method at the moment. We will notify you when it is supported.

EF Core: the SQLiteFunctions class is on our roadmap.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Filtering by parsed int, if string field was parseable to int (inside Linq to Entites)

Post by Shalex » Mon 25 Jun 2018 17:31

EF6: you can check the list of available methods of Devart.Data.SQLite.Entity.SQLiteFunctions via IntelliSense.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Filtering by parsed int, if string field was parseable to int (inside Linq to Entites)

Post by Shalex » Wed 18 Jul 2018 15:41

The SQLiteFunctions.Printf method in EF4/EF5/EF6 is implemented. We will notify you when the new build is available for download.

With SQLiteFunctions.Printf, you can perform your task this way:

Code: Select all

var query = context.Table
  .Where(t => t.StringColumn == SQLiteFunctions.Printf("%d", t.StringColumn))
  .ToList();
Refer to https://www.sqlite.org/lang_corefunc.html#printf.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Filtering by parsed int, if string field was parseable to int (inside Linq to Entites)

Post by Shalex » Fri 20 Jul 2018 11:54

New build of dotConnect for SQLite 5.11.1190 is available for download: viewtopic.php?f=29&t=37472.

bairog
Posts: 120
Joined: Mon 29 Apr 2013 09:05

Re: Filtering by parsed int, if string field was parseable to int (inside Linq to Entites)

Post by bairog » Thu 23 Aug 2018 05:23

Shalex wrote: Wed 18 Jul 2018 15:41With SQLiteFunctions.Printf, you can perform your task this way:

Code: Select all

var query = context.Table
  .Where(t => t.StringColumn == SQLiteFunctions.Printf("%d", t.StringColumn))
  .ToList();
Refer to https://www.sqlite.org/lang_corefunc.html#printf.
You are suggesting me to replace .Where(t => Int32.TryParse(t.StringColumn, out StringAsInt)) with .Where(t => t.StringColumn == SQLiteFunctions.Printf("%d", t.StringColumn)), right?
That will not work for example when t.StringColumn equals "01" (SQLiteFunctions.Printf("%d", t.StringColumn) will return "1"), or "001", or "0001", etc.
So for now I've replaced my initial code

Code: Select all

Int32 CodeAsInt = Int32.MinValue;
var result = MyDBContext.MyEntites.Where(e => e.Name == "test").AsEnumerable().
Where(e => (Int32.TryParse(e.StringCode, out  CodeAsInt)) && (CodeAsInt == 333)).Tolist()
with

Code: Select all

var result = MyDBContext.MyEntites.Where(e => (e.Name == "test")).
Where(e => SQLiteFunctions.Printf("%d", t.StringCode) == 333.ToString()).Tolist()
But for some reason SQLiteFunctions.Printf("%d", "abcd") will return "0" (not some special value like Int32.MinValue.ToString() or something similar)
So my code will not work if I search for 0 code instead of 333 (it will return all entites with zero code and all entities where code is not parseable to int).
That is not a great drawback for us (we never search for 0 code), but this behaviour must be taken into account.

bairog
Posts: 120
Joined: Mon 29 Apr 2013 09:05

Re: Filtering by parsed int, if string field was parseable to int (inside Linq to Entites)

Post by bairog » Tue 28 Aug 2018 05:36

So SQLiteFunctions.Printf("%d", "abcd") returning "0" (not some special value like Int32.MinValue.ToString() or something similar) - is that a bug or a feature? Is it documented somewhere?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Filtering by parsed int, if string field was parseable to int (inside Linq to Entites)

Post by Shalex » Mon 03 Sep 2018 16:09

SQLiteFunctions.Printf is a wrapper of the SQLite printf function.

If the functionality of the underlying printf core function doesn't suit your scenario, you can implement a custom function, register and use it via EF. For example, refer to viewtopic.php?t=34546#p120333 - this sample demonstrates how to register a custom function via EF6, additionally you should map the function to use it in your LINQ queries.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Filtering by parsed int, if string field was parseable to int (inside Linq to Entites)

Post by Shalex » Mon 21 Sep 2020 17:30

The SQLiteFunctions class, which allows using SQLite-specific functions in LINQ to Entities, is supported in EF Core: viewtopic.php?f=29&t=42250.

Post Reply