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

  • 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 == "rupertJpupkin@att.net"). // <- 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 ("rupertJpupkin@att.net") 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?


    Monday, November 26, 2012 11:26 PM

Answers

  • 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

    Wednesday, November 28, 2012 6:34 AM

All replies

  •  var query = db.Table<Locations>().Where(l => l.PlatypusId == "rupertJpupkin@att.net"). // <- This is the sticking
     point

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

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

    Tuesday, November 27, 2012 7:20 AM
  •  var query = db.Table<Locations>().Where(l => l.PlatypusId == "rupertJpupkin@att.net"). // <- 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#?
    Tuesday, November 27, 2012 3:48 PM
  • 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

    Wednesday, November 28, 2012 6:34 AM
  • 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);

    Wednesday, November 28, 2012 3:37 PM