Page 1 of 1

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

Posted: Fri 22 Jun 2018 06:40
by bairog
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()?

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

Posted: Mon 25 Jun 2018 17:24
by Shalex
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.

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

Posted: Mon 25 Jun 2018 17:31
by Shalex
EF6: you can check the list of available methods of Devart.Data.SQLite.Entity.SQLiteFunctions via IntelliSense.

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

Posted: Wed 18 Jul 2018 15:41
by Shalex
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.

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

Posted: Fri 20 Jul 2018 11:54
by Shalex
New build of dotConnect for SQLite 5.11.1190 is available for download: viewtopic.php?f=29&t=37472.

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

Posted: Thu 23 Aug 2018 05:23
by bairog
Shalex wrote:
Wed 18 Jul 2018 15:41
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.
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.

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

Posted: Tue 28 Aug 2018 05:36
by bairog
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?

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

Posted: Mon 03 Sep 2018 16:09
by Shalex
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.