0

I have the following code, which pulls data from the database for a selected list of GUID.

var sitepks = Session["ListOfSites"].ToString().Split(',').ToList();
            var listOfSitePKs = new List<SiteGuid>();
            for (var index = 0; index < sitepks.Count; index++)
            {
                var site = sitepks[index];
                var guidForm = new Regex(
                    @"^(\{{0,1}([0-9a-fA-F]){8}-([0-9a-fA-F]){4}-([0-9a-fA-F]){4}-([0-9a-fA-F]){4}-([0-9a-fA-F]){12}\}{0,1})$",
                    RegexOptions.Compiled | RegexOptions.IgnoreCase);
                if (guidForm.Matches(site).Any()) //check if site is GUID
                {
                    var newsite1 = new SiteGuid();
                    newsite1.PK_Site = Guid.Parse(sitepks[index]);
                    listOfSitePKs.Add(newsite1);
                }
            }
            using (var db = _db.GetDatabase(_organizationWebHandler.OrgName))
            {
                var siteresults = db.Site.Join(db.AncestryTree, x => x.PK_Site, y => y.PK_Site,
                    (x, y) => 
                        new SiteHeader{ 
                        PK_Site = x.PK_Site,
                            SiteId=x.SiteID,
                        Notes = x.Notes,
                        SiteAncestry = y.Ancestry,
                        Slope = x.Slope,
                        Aspect = x.Aspect,
                        Elevation = x.Elevation,
                        DDLat = (x.Locators.FirstOrDefault(t => t.IsPrimary == true) != null)
                            ? x.Locators.First(q => q.IsPrimary == true).DDLat
                            : null,
                        DDLong = (x.Locators.FirstOrDefault(t => t.IsPrimary == true) != null)
                            ? x.Locators.First(q => q.IsPrimary == true).DDLat
                            : null
                    }).Join(listOfSitePKs, x=>x.PK_Site, y=>y.PK_Site,(x,y) => x).ToList();

I believe this does the Site join with Ancestry in SQL and then with that result joins with my list of GUIDs. My first question is the above statement true. If it isn't true and somehow EF knows to creates SQL with an IN Clause (in ('guid','guid'), then I am good. However, since I don't believe that is true, my question is, how do I make it run in the SQL instead of bringing back every row of Site before it does the join? I tried contains, but it doesn't seem to work with Guids.(It basically doesn't allow contains)

done_merson
  • 2,800
  • 2
  • 22
  • 30
  • 1
    Here is something for reference, How to view SQL Generated by EF: https://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework – S. Walker Oct 08 '21 at 23:46
  • Please always use a tag of the exact EF version you're using. And yes, as the above comment says, you can answer most of this yourself by looking at the generated SQL which I consider a basic skill for anyone working with an ORM. But to give it away a bit: don't create a list of `SiteGuid`s but simply the `PK_Site` values and use `Contains` in the second query. – Gert Arnold Oct 09 '21 at 14:35
  • As mentions at the end, contains doesn't work for a list of GUIDs. – done_merson Oct 10 '21 at 03:13

0 Answers0