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
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.
Re: Filtering by parsed int, if string field was parseable to int (inside Linq to Entites)
Posted: Mon 21 Sep 2020 17:30
by Shalex
The SQLiteFunctions class, which allows using SQLite-specific functions in LINQ to Entities, is supported in EF Core:
viewtopic.php?f=29&t=42250.