Need Help with a LINQ query analagous to an "IN" clause in SQL

Question

I've been struggling mightily with writing some LINQ queries. I want to return the records with a matching PlatypusID (from an array or List<T> of IDs that should be considered a match); I'm getting close (I think/hope) with this:

        public List<Locations> GetLocationsForPlatypiAndTimeRange(List<string> PlatypiIds, DateTime EarliestToShow,
                                                                  DateTime LatestToShow)
        {
            List<Locations> listLocs = new List<Locations>();
            using (var db = new SQLiteConnection(SQLitePath))
            {
                var query = db.Table<Locations>().Where(l => l.PlatypusId == "[e-mail]"). // <- This is the sticking point
                               Where(l => l.SentTimeLocal >= EarliestToShow).
                               Where(l => l.SentTimeLocal <= LatestToShow).
                               OrderBy(l => l.SentTimeLocal);
                foreach (var location in query)
                {
                    var loc = new Locations()
                    {
                        PlatypusId = location.PlatypusId,
                        PlaceName = location.PlaceName,
                        Latitude = location.Latitude,
                        Longitude = location.Longitude,
                        SentTimeLocal = location.SentTimeLocal,
                        ReceivedTimeLocal = location.ReceivedTimeLocal
                    };
                    listLocs.Add(loc);
                }
            }
            return listLocs;
        }       

The problem is the hardcoded PlatypusId ("[e-mail]") that is sought; I need it to find any in the PlatypiIds List<string>, like the SQL "WHERE PLATYPUSID IN (BLA)"

After searching online and re-reading through Albahari's chapters on LINQ in "C# 5.0 In a Nut's Hell," I've tried
a variety of things, none of which compile, such as:

var query = db.Table<Locations>().Where(PlatypiIds.Contains(l => l.PlatypusId));
var query = db.Table<Locations>().Where(l => l.PlatypusId.Contains(PlatypiIds));

Does anybody know how to solve this dilemma?

Answer 1

 var query = db.Table<Locations>().Where(l => l.PlatypusId == "[e-mail]"). // <- This is the sticking
 point

In VB you'd make an array so it would be

.Where({"eMailAdres1", "eMailAdres2", <...>}.Contains(function (l) l.PlatypusID)))

Answer 2

 var query = db.Table<Locations>().Where(l => l.PlatypusId == "[e-mail]"). // <- This is the sticking
 point

In VB you'd make an array so it would be

.Where({"eMailAdres1", "eMailAdres2", <...>}.Contains(function (l) l.PlatypusID)))

I wouldn't mind a Victoria Bitter, but does anybody know how to do this in C#?

Answer 3

Hi B.Clay Shannon,

Welcome to the MSDN forum.

Please check whether the following works:

         public List<Locations> GetLocationsForPlatypiAndTimeRange(List<string> PlatypiIds, DateTime EarliestToShow, DateTime LatestToShow)
         {
             List<Locations> listLocs = new List<Locations>();
             using (var db = new SQLiteConnection(SQLitePath))
             {
                 foreach (var item in PlatypiIds)
                 {
                     var query = db.Table<Locations>().Where(l => l.PlatypusId == item && l => l.SentTimeLocal >= EarliestToShow && l => l.SentTimeLocal <= LatestToShow).OrderBy(l => l.SentTimeLocal);
                     if (query != null)
                     {
                         foreach (var q in query)
                         {
                             listLocs.Add(q);
                         }
                     }
             }
             return listLocs;
         } 

Have a nice day.

---

Alexander Sun [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Hi B.Clay Shannon,

Welcome to the MSDN forum.

Please check whether the following works:

         public List<Locations> GetLocationsForPlatypiAndTimeRange(List<string> PlatypiIds, DateTime EarliestToShow, DateTime LatestToShow)
         {
             List<Locations> listLocs = new List<Locations>();
             using (var db = new SQLiteConnection(SQLitePath))
             {
                 foreach (var item in PlatypiIds)
                 {
                     var query = db.Table<Locations>().Where(l => l.PlatypusId == item && l => l.SentTimeLocal >= EarliestToShow && l => l.SentTimeLocal <= LatestToShow).OrderBy(l => l.SentTimeLocal);
                     if (query != null)
                     {
                         foreach (var q in query)
                         {
                             listLocs.Add(q);
                         }
                     }
             }
             return listLocs;
         } 

Have a nice day.

---

Alexander Sun [MSFT]
MSDN Community Support | Feedback to us

Very elegant!/why didn't I think of that? Thanks!

Actually, I had to edit it a bit; at first, it caused 101 Warnings and 8 errors, all seemingly having nothing to do with this code, so here's what I ended up with (an easy fix, based on Sun's code):

                    //var query = db.Table<Locations>().Where(l => l.PersonId == item && l=> l.SentTimeLocal >=
                    //EarliestToShow && l=>
                    //l.SentTimeLocal <= LatestToShow).
                    //OrderBy(l => l.SentTimeLocal); <-- This caused 109 errors, more than the error window would even display
                    var query = db.Table<Locations>().Where(l => l.PersonId == item).
                        Where(l=> l.SentTimeLocal >= EarliestToShow).
                        Where(l=> l.SentTimeLocal <= LatestToShow).
                        OrderBy(l => l.SentTimeLocal);