Why can I not query a DATETIME field using EF6 and SQlite?SQLite not storing decimals correctlyHow can I convert a Unix timestamp to DateTime and vice versa?How can one see the structure of a table in SQLite?LINQ - Different results with LINQ to SQL vs LINQPadSQLite query returns nothingSQLite sort order on datetime column populated from C# with DateTimeOffsetValueError when querying dates with SQLiteSQLite returns DATETIME column value as stringSqlDependency select today recordsHow can I get the actual datetime values from a SQLite table?SQLite not returning rows, but manual execution of query does
Papers on ArXiv as main references
Unary Enumeration
Is there a simple example that empirical evidence is misleading?
If I arrive in the UK, and then head to mainland Europe, does my Schengen visa 90 day limit start when I arrived in the UK, or mainland Europe?
How did the Allies achieve air superiority on Sicily?
Why does the Starter Set wizard have six spells in their spellbook?
Are there guidelines for finding good names for LaTeX 2e packages and control sequences defined in these packages?
Why A=2 and B=1 in the call signs for Spirit and Opportunity?
Is this homebrew "Cactus Grenade" cantrip balanced?
Using too much dialogue?
Physical only checkdb is failing, but full one is completed successfully
Ribbon Cable Cross Talk - Is there a fix after the fact?
What is the purpose of the yellow wired panels on the IBM 360 Model 20?
Moons and messages
Are PMR446 walkie-talkies legal in Switzerland?
Why was this character made Grand Maester?
How to create a `range`-like iterable object of floats?
Are there historical examples of audiences drawn to a work that was "so bad it's good"?
Is it normal to "extract a paper" from a master thesis?
Flatten not working
Why did OJ Simpson's trial take 9 months?
Cisco 3750X Power Cable
Complications of displaced core material?
Who wrote “A writer only begins a book. A reader finishes it.”?
Why can I not query a DATETIME field using EF6 and SQlite?
SQLite not storing decimals correctlyHow can I convert a Unix timestamp to DateTime and vice versa?How can one see the structure of a table in SQLite?LINQ - Different results with LINQ to SQL vs LINQPadSQLite query returns nothingSQLite sort order on datetime column populated from C# with DateTimeOffsetValueError when querying dates with SQLiteSQLite returns DATETIME column value as stringSqlDependency select today recordsHow can I get the actual datetime values from a SQLite table?SQLite not returning rows, but manual execution of query does
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I have a WPF application which uses EF6 and Sqlite. I'm trying to run a relatively simple query which compares dates. I have read a thousand articles on sqlite and dates and I'm still stuck.
I don't think this has anything to do with the time part of the comparison (i.e. trying to compare date only). I understand that I could use a start and end datetime to capture records which fall between. I think this is to do with the actual sql that is being generated by the orm.
I think the issue comes from the SQL generated by EF6. In SQlite my table has a DATETIME field which is a string value in the following format: "2019-03-23 00:00:00.000".
When I run the query as part of the application in VS debug mode, the query returns no results and I can see from the logged sql, the parameter in the where clause is p__linq__0: '23/03/2019 00:00:00'
When I run the very same query using Linqpad (targeting the same db with the same dll reference) the query does indeed return records as I expect and I noticed that the sql generated by linqpad uses this format for the parameter @p__linq__0 DateTime2 = '2019-03-23 00:00:00.0000000'
In the application, I reference System.Data.SQLite (v 1.0.110.0) - I'm not sure what linqpad uses.
I don't know how to influence the generated SQL in the application but linqpad suggests that it should be possible to generate some compatible sql.
In case it helps or I'm barking up the wrong tree, here is my linq query. The comparison is with the parameter "today" which creates the different sql parameters as above.
public IEnumerable<Menu> GetTodaysMenus()
DateTime today = DateTime.Now.Date;
var menus = _context.Menus
.Select(x => new
x,
x.SalesBusinessType,
Date = x.MenuDays.Select(y => y.CalendarDate)
)
.Where(x => x.x.DeletedAt == null &&
x.x.Published == true &&
x.Date.Contains(today))
.AsEnumerable()
.Select(x => x.x);
return menus;
I can reproduce the scenario and I'm able to change the database configuration but I'm not sure what to do. I want to be able to use date comparison in my application as I seem to be able to do when using linqpad.
UPDATE:
This updated query gets the record I was expecting. I'm still unsure why and what was "wrong" with the original
public IEnumerable<Menu> GetTodaysMenus()
string today = DateTime.Now.Date.ToString("yyyy-MM-dd HH:mm:ss.fff");
var menus = _context.Menus
.Select(x => new
x,
x.SalesBusinessType,
x.MenuDays,
Date = x.MenuDays.Select(y => y.CalendarDate.ToString())
)
.Where(x => x.x.DeletedAt == null &&
x.x.Published == true &&
x.Date.Contains(today))
.AsEnumerable()
.Select(x => x.x);
return menus;
UPDATE 2:
Direct comparisons work like this for example (DateTime == today) or in this example DateTime collection Contains(today) however ranges (i.e. datetime >= today) don't work.
c# wpf sqlite entity-framework-6 linqpad
asked Mar 23 at 22:13
JonJon
8712
|
show 7 more comments
I have a WPF application which uses EF6 and Sqlite. I'm trying to run a relatively simple query which compares dates. I have read a thousand articles on sqlite and dates and I'm still stuck.
I don't think this has anything to do with the time part of the comparison (i.e. trying to compare date only). I understand that I could use a start and end datetime to capture records which fall between. I think this is to do with the actual sql that is being generated by the orm.
I think the issue comes from the SQL generated by EF6. In SQlite my table has a DATETIME field which is a string value in the following format: "2019-03-23 00:00:00.000".
When I run the query as part of the application in VS debug mode, the query returns no results and I can see from the logged sql, the parameter in the where clause is p__linq__0: '23/03/2019 00:00:00'
When I run the very same query using Linqpad (targeting the same db with the same dll reference) the query does indeed return records as I expect and I noticed that the sql generated by linqpad uses this format for the parameter @p__linq__0 DateTime2 = '2019-03-23 00:00:00.0000000'
In the application, I reference System.Data.SQLite (v 1.0.110.0) - I'm not sure what linqpad uses.
I don't know how to influence the generated SQL in the application but linqpad suggests that it should be possible to generate some compatible sql.
In case it helps or I'm barking up the wrong tree, here is my linq query. The comparison is with the parameter "today" which creates the different sql parameters as above.
public IEnumerable<Menu> GetTodaysMenus()
DateTime today = DateTime.Now.Date;
var menus = _context.Menus
.Select(x => new
x,
x.SalesBusinessType,
Date = x.MenuDays.Select(y => y.CalendarDate)
)
.Where(x => x.x.DeletedAt == null &&
x.x.Published == true &&
x.Date.Contains(today))
.AsEnumerable()
.Select(x => x.x);
return menus;
I can reproduce the scenario and I'm able to change the database configuration but I'm not sure what to do. I want to be able to use date comparison in my application as I seem to be able to do when using linqpad.
UPDATE:
This updated query gets the record I was expecting. I'm still unsure why and what was "wrong" with the original
public IEnumerable<Menu> GetTodaysMenus()
string today = DateTime.Now.Date.ToString("yyyy-MM-dd HH:mm:ss.fff");
var menus = _context.Menus
.Select(x => new
x,
x.SalesBusinessType,
x.MenuDays,
Date = x.MenuDays.Select(y => y.CalendarDate.ToString())
)
.Where(x => x.x.DeletedAt == null &&
x.x.Published == true &&
x.Date.Contains(today))
.AsEnumerable()
.Select(x => x.x);
return menus;
UPDATE 2:
Direct comparisons work like this for example (DateTime == today) or in this example DateTime collection Contains(today) however ranges (i.e. datetime >= today) don't work.
c# wpf sqlite entity-framework-6 linqpad
asked Mar 23 at 22:13
JonJon
8712
What is the data type ofCalendarDate? You say it's a DateTime field, but then you say it's a string. DBs do not store Dates or Times as strings, but will allow you to query against a formatted string.
– juharr
Mar 23 at 22:22
1
Also you don't need that intermediateSelectyou can havex.MenuDays.Select(y => y.CalendarDat).Contains(today)in theWhereclause.
– juharr
Mar 23 at 22:24
1
@mjwills In that case the OP might want to try the query with the date formatted as a string instead.
– juharr
Mar 23 at 22:30
1
@mjwills - I'm not getting an error, rather no records returned. I cant use a string for today in the query as the entity has the field as a c# DateTime. I don't know what StartsWith is as the Date field on the anonymous is a collection of DateTime. The Where targets the Date which is on the anonymous so I think needs to come after the select.
– Jon
Mar 23 at 22:35
1
What if you try something likex.MenuDays.Select(y => y.CalendarDate.ToString()).Contains(todayAsString). That should either result is some type of SQL that will allow you to compare the column to a formatted date string that should match, or EF will give an error.
– juharr
Mar 24 at 11:43
|
show 7 more comments
I have a WPF application which uses EF6 and Sqlite. I'm trying to run a relatively simple query which compares dates. I have read a thousand articles on sqlite and dates and I'm still stuck.
I don't think this has anything to do with the time part of the comparison (i.e. trying to compare date only). I understand that I could use a start and end datetime to capture records which fall between. I think this is to do with the actual sql that is being generated by the orm.
I think the issue comes from the SQL generated by EF6. In SQlite my table has a DATETIME field which is a string value in the following format: "2019-03-23 00:00:00.000".
When I run the query as part of the application in VS debug mode, the query returns no results and I can see from the logged sql, the parameter in the where clause is p__linq__0: '23/03/2019 00:00:00'
When I run the very same query using Linqpad (targeting the same db with the same dll reference) the query does indeed return records as I expect and I noticed that the sql generated by linqpad uses this format for the parameter @p__linq__0 DateTime2 = '2019-03-23 00:00:00.0000000'
In the application, I reference System.Data.SQLite (v 1.0.110.0) - I'm not sure what linqpad uses.
I don't know how to influence the generated SQL in the application but linqpad suggests that it should be possible to generate some compatible sql.
In case it helps or I'm barking up the wrong tree, here is my linq query. The comparison is with the parameter "today" which creates the different sql parameters as above.
public IEnumerable<Menu> GetTodaysMenus()
DateTime today = DateTime.Now.Date;
var menus = _context.Menus
.Select(x => new
x,
x.SalesBusinessType,
Date = x.MenuDays.Select(y => y.CalendarDate)
)
.Where(x => x.x.DeletedAt == null &&
x.x.Published == true &&
x.Date.Contains(today))
.AsEnumerable()
.Select(x => x.x);
return menus;
I can reproduce the scenario and I'm able to change the database configuration but I'm not sure what to do. I want to be able to use date comparison in my application as I seem to be able to do when using linqpad.
UPDATE:
This updated query gets the record I was expecting. I'm still unsure why and what was "wrong" with the original
public IEnumerable<Menu> GetTodaysMenus()
string today = DateTime.Now.Date.ToString("yyyy-MM-dd HH:mm:ss.fff");
var menus = _context.Menus
.Select(x => new
x,
x.SalesBusinessType,
x.MenuDays,
Date = x.MenuDays.Select(y => y.CalendarDate.ToString())
)
.Where(x => x.x.DeletedAt == null &&
x.x.Published == true &&
x.Date.Contains(today))
.AsEnumerable()
.Select(x => x.x);
return menus;
UPDATE 2:
Direct comparisons work like this for example (DateTime == today) or in this example DateTime collection Contains(today) however ranges (i.e. datetime >= today) don't work.
c# wpf sqlite entity-framework-6 linqpad
asked Mar 23 at 22:13
JonJon
8712
I have a WPF application which uses EF6 and Sqlite. I'm trying to run a relatively simple query which compares dates. I have read a thousand articles on sqlite and dates and I'm still stuck.
I don't think this has anything to do with the time part of the comparison (i.e. trying to compare date only). I understand that I could use a start and end datetime to capture records which fall between. I think this is to do with the actual sql that is being generated by the orm.
I think the issue comes from the SQL generated by EF6. In SQlite my table has a DATETIME field which is a string value in the following format: "2019-03-23 00:00:00.000".
When I run the query as part of the application in VS debug mode, the query returns no results and I can see from the logged sql, the parameter in the where clause is p__linq__0: '23/03/2019 00:00:00'
When I run the very same query using Linqpad (targeting the same db with the same dll reference) the query does indeed return records as I expect and I noticed that the sql generated by linqpad uses this format for the parameter @p__linq__0 DateTime2 = '2019-03-23 00:00:00.0000000'
In the application, I reference System.Data.SQLite (v 1.0.110.0) - I'm not sure what linqpad uses.
I don't know how to influence the generated SQL in the application but linqpad suggests that it should be possible to generate some compatible sql.
In case it helps or I'm barking up the wrong tree, here is my linq query. The comparison is with the parameter "today" which creates the different sql parameters as above.
public IEnumerable<Menu> GetTodaysMenus()
DateTime today = DateTime.Now.Date;
var menus = _context.Menus
.Select(x => new
x,
x.SalesBusinessType,
Date = x.MenuDays.Select(y => y.CalendarDate)
)
.Where(x => x.x.DeletedAt == null &&
x.x.Published == true &&
x.Date.Contains(today))
.AsEnumerable()
.Select(x => x.x);
return menus;
I can reproduce the scenario and I'm able to change the database configuration but I'm not sure what to do. I want to be able to use date comparison in my application as I seem to be able to do when using linqpad.
UPDATE:
This updated query gets the record I was expecting. I'm still unsure why and what was "wrong" with the original
public IEnumerable<Menu> GetTodaysMenus()
string today = DateTime.Now.Date.ToString("yyyy-MM-dd HH:mm:ss.fff");
var menus = _context.Menus
.Select(x => new
x,
x.SalesBusinessType,
x.MenuDays,
Date = x.MenuDays.Select(y => y.CalendarDate.ToString())
)
.Where(x => x.x.DeletedAt == null &&
x.x.Published == true &&
x.Date.Contains(today))
.AsEnumerable()
.Select(x => x.x);
return menus;
UPDATE 2:
Direct comparisons work like this for example (DateTime == today) or in this example DateTime collection Contains(today) however ranges (i.e. datetime >= today) don't work.
c# wpf sqlite entity-framework-6 linqpad
c# wpf sqlite entity-framework-6 linqpad
asked Mar 23 at 22:13
JonJon
8712
asked Mar 23 at 22:13
JonJon
8712
edited Mar 26 at 10:54
Jon
asked Mar 23 at 22:13
JonJon
8712
asked Mar 23 at 22:13
JonJon
8712
asked Mar 23 at 22:13
JonJon
8712
8712
What is the data type ofCalendarDate? You say it's a DateTime field, but then you say it's a string. DBs do not store Dates or Times as strings, but will allow you to query against a formatted string.
– juharr
Mar 23 at 22:22
1
Also you don't need that intermediateSelectyou can havex.MenuDays.Select(y => y.CalendarDat).Contains(today)in theWhereclause.
– juharr
Mar 23 at 22:24
1
@mjwills In that case the OP might want to try the query with the date formatted as a string instead.
– juharr
Mar 23 at 22:30
1
@mjwills - I'm not getting an error, rather no records returned. I cant use a string for today in the query as the entity has the field as a c# DateTime. I don't know what StartsWith is as the Date field on the anonymous is a collection of DateTime. The Where targets the Date which is on the anonymous so I think needs to come after the select.
– Jon
Mar 23 at 22:35
1
What if you try something likex.MenuDays.Select(y => y.CalendarDate.ToString()).Contains(todayAsString). That should either result is some type of SQL that will allow you to compare the column to a formatted date string that should match, or EF will give an error.
– juharr
Mar 24 at 11:43
|
show 7 more comments
What is the data type ofCalendarDate? You say it's a DateTime field, but then you say it's a string. DBs do not store Dates or Times as strings, but will allow you to query against a formatted string.
– juharr
Mar 23 at 22:22
1
Also you don't need that intermediateSelectyou can havex.MenuDays.Select(y => y.CalendarDat).Contains(today)in theWhereclause.
– juharr
Mar 23 at 22:24
1
@mjwills In that case the OP might want to try the query with the date formatted as a string instead.
– juharr
Mar 23 at 22:30
1
@mjwills - I'm not getting an error, rather no records returned. I cant use a string for today in the query as the entity has the field as a c# DateTime. I don't know what StartsWith is as the Date field on the anonymous is a collection of DateTime. The Where targets the Date which is on the anonymous so I think needs to come after the select.
– Jon
Mar 23 at 22:35
1
What if you try something likex.MenuDays.Select(y => y.CalendarDate.ToString()).Contains(todayAsString). That should either result is some type of SQL that will allow you to compare the column to a formatted date string that should match, or EF will give an error.
– juharr
Mar 24 at 11:43
What is the data type of
CalendarDate? You say it's a DateTime field, but then you say it's a string. DBs do not store Dates or Times as strings, but will allow you to query against a formatted string.– juharr
Mar 23 at 22:22
What is the data type of
CalendarDate? You say it's a DateTime field, but then you say it's a string. DBs do not store Dates or Times as strings, but will allow you to query against a formatted string.– juharr
Mar 23 at 22:22
1
1
Also you don't need that intermediate
Select you can have x.MenuDays.Select(y => y.CalendarDat).Contains(today) in the Where clause.– juharr
Mar 23 at 22:24
Also you don't need that intermediate
Select you can have x.MenuDays.Select(y => y.CalendarDat).Contains(today) in the Where clause.– juharr
Mar 23 at 22:24
1
1
@mjwills In that case the OP might want to try the query with the date formatted as a string instead.
– juharr
Mar 23 at 22:30
@mjwills In that case the OP might want to try the query with the date formatted as a string instead.
– juharr
Mar 23 at 22:30
1
1
@mjwills - I'm not getting an error, rather no records returned. I cant use a string for today in the query as the entity has the field as a c# DateTime. I don't know what StartsWith is as the Date field on the anonymous is a collection of DateTime. The Where targets the Date which is on the anonymous so I think needs to come after the select.
– Jon
Mar 23 at 22:35
@mjwills - I'm not getting an error, rather no records returned. I cant use a string for today in the query as the entity has the field as a c# DateTime. I don't know what StartsWith is as the Date field on the anonymous is a collection of DateTime. The Where targets the Date which is on the anonymous so I think needs to come after the select.
– Jon
Mar 23 at 22:35
1
1
What if you try something like
x.MenuDays.Select(y => y.CalendarDate.ToString()).Contains(todayAsString). That should either result is some type of SQL that will allow you to compare the column to a formatted date string that should match, or EF will give an error.– juharr
Mar 24 at 11:43
What if you try something like
x.MenuDays.Select(y => y.CalendarDate.ToString()).Contains(todayAsString). That should either result is some type of SQL that will allow you to compare the column to a formatted date string that should match, or EF will give an error.– juharr
Mar 24 at 11:43
|
show 7 more comments
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55318870%2fwhy-can-i-not-query-a-datetime-field-using-ef6-and-sqlite%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55318870%2fwhy-can-i-not-query-a-datetime-field-using-ef6-and-sqlite%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What is the data type of
CalendarDate? You say it's a DateTime field, but then you say it's a string. DBs do not store Dates or Times as strings, but will allow you to query against a formatted string.– juharr
Mar 23 at 22:22
1
Also you don't need that intermediate
Selectyou can havex.MenuDays.Select(y => y.CalendarDat).Contains(today)in theWhereclause.– juharr
Mar 23 at 22:24
1
@mjwills In that case the OP might want to try the query with the date formatted as a string instead.
– juharr
Mar 23 at 22:30
1
@mjwills - I'm not getting an error, rather no records returned. I cant use a string for today in the query as the entity has the field as a c# DateTime. I don't know what StartsWith is as the Date field on the anonymous is a collection of DateTime. The Where targets the Date which is on the anonymous so I think needs to come after the select.
– Jon
Mar 23 at 22:35
1
What if you try something like
x.MenuDays.Select(y => y.CalendarDate.ToString()).Contains(todayAsString). That should either result is some type of SQL that will allow you to compare the column to a formatted date string that should match, or EF will give an error.– juharr
Mar 24 at 11:43