Cannot make WHERE IN clause working properly in LINQ to Entities

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

Cannot make WHERE IN clause working properly in LINQ to Entities

Post by bairog » Fri 16 Jun 2017 05:41

Hello.
I'm trying to select items from database with List in a “WHERE” condition.
Assume we have the following entities:

Code: Select all

public class Message
    {
        public Guid Id { get; set; }
        public String Text { get; set; }
        public bool IsPrivate { get; set; }
        public Guid UserId { get; set; }        
    }

    public class User
    {
        public Guid Id { get; set; }
        public String Name { get; set; }
        public int Age { get; set; }       
    } 
I want to get messages posted by two users. I've tried the following code:

Code: Select all

messages = ActiveContext.Messages.Where(m => listOfUserIds.Contains(m.UserId)).ToList();
That doesn't work (0 items returned while 2 exists in database);
I've tried even the following approach:

Code: Select all

var guid1 = listOfUserIds[0];
var query = ActiveContext.Messages.Where(m => m.UserId.Equals(guid1));
var guid2 = listOfUserIds[1];
query = query.Concat(ActiveContext.Messages.Where(m => m.UserId.Equals(guid2)));
messages = query.ToList();
It does't work also.
What's wrong with my code?
I use dotConnect for SQLite 5.3.583.

Sample project I've uploaded here
Click AddUsersAndMessages() button first to create database (on disk D) and then click ReadMessages() button (it uses all code above).
Last edited by bairog on Mon 26 Jun 2017 18:27, edited 2 times in total.

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

Re: Cannot make WHERE IN clause working properly in LINQ to Entities

Post by Shalex » Fri 16 Jun 2017 15:54

Thank you for your report. We have reproduced the issue and are investigating it. We will notify you about the result.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Cannot make WHERE IN clause working properly in LINQ to Entities

Post by Pinturiccio » Thu 22 Jun 2017 10:30

We have fixed the bug with SQL generation of literals for System.Guid values. We will notify when the corresponding build of dotConnect for SQLite is available for download.

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

Re: Cannot make WHERE IN clause working properly in LINQ to Entities

Post by bairog » Fri 23 Jun 2017 05:07

Pinturiccio wrote:We have fixed the bug with SQL generation of literals for System.Guid values
So the problem is specific for .Contains(System.Guid)?
The bug doesn't affect other types - for example with .Contains(System.Int32) or .Contains(System.String)?

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Cannot make WHERE IN clause working properly in LINQ to Entities

Post by Pinturiccio » Fri 23 Jun 2017 16:08

bairog wrote:So the problem is specific for .Contains(System.Guid)?
Yes, it is. This issue is already fixed, and the new build of dotConnect for SQLite with the fix is available for download. Please try the latest build and tell us about the results.
bairog wrote:The bug doesn't affect other types - for example with .Contains(System.Int32) or .Contains(System.String)?
Yes, that’s true. Contains(System.Int32) or .Contains(System.String) works correctly.

New build of dotConnect for SQLite 5.9.913 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/sqlite/download.html (trial version) or from Customer Portal (for users with valid subscription only).
For more information, please refer to viewtopic.php?t=35588

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

Re: Cannot make WHERE IN clause working properly in LINQ to Entities

Post by bairog » Mon 26 Jun 2017 18:26

Pinturiccio wrote:This issue is already fixed, and the new build of dotConnect for SQLite with the fix is available for download. Please try the latest build and tell us about the results.
I confirm that with latest SQLite 5.9.931 my code works correctly.
Thanks.

Post Reply