0

I have an entity framework query where items are displayed based on a variable set of user-defined criteria. The final query is supposed to include the related entity "Subjects", but only does so in an intermittent fashion. That is, in the same result set, some results will include their subjects while others will not. To make matters even more bizarre, the same record "a" will include its related subjects in one request, but not in another, even though I am using the same select code in both cases. Here's the relevant snippet of the final select:

 var newQuery = query.Select(l => new
            {
                RecordId = l.RecordId,
                RawFileId = l.RawFileId,
                Subjects = l.RecordSubjects.Where(s => s.Subject.ClientId == data.ClientId && l.RecordId == s.RecordId && s.IsActive).Select(s => new { Name = s.Subject.Name, SubjectId = s.Subject.SubjectId, Description = s.Subject.Description }),

                Url = (l.Url == null) ? "#" : l.Url, //make sure all results display a valid Url, even if the field is null
                RegionCode = l.RegionCode ?? "",
                RegionName = l.Region.Name ?? "",
                Year = l.Year

            }

I've eliminated a bunch of additional fields and other related entities for clarity.

UPDATE: To further clarify, I can run the same exact query and have the subjects appear for a specific record the first time, and have nothing show up the next. So it can't be anything about the data, I don't think.

acullen72
  • 817
  • 2
  • 9
  • 19
  • Can you provide sample s and data values. – Mutant May 11 '15 at 21:30
  • I believe the "intermittent" fashion would be exactly related to subjects where the subject's client id was equal to the current data object's id, and the query's primary key was equal to the subject's primary key, and where the subject was still active. – Travis J May 11 '15 at 21:32
  • Trying to figure out how to do that and still honor my NDA... – acullen72 May 11 '15 at 21:32
  • Travis - trying to follow your suggestion, and realized my editing of the code made things a bit less clear. Fixed the Id names to make things clearer... I hope. – acullen72 May 11 '15 at 21:37
  • @acullen72 - My comment was also a little obfuscated. My point was that the Where clause for selecting the subjects that you use is probably why some are loaded and some not. `l.RecordSubjects.Where(s => s.Subject.ClientId == data.ClientId && l.RecordId == s.RecordId && s.IsActive)` – Travis J May 11 '15 at 21:41

1 Answers1

0

Try throwing a .AsEnumerable(). before the Select, i.e., query.AsEnumerable().Select( This would force retrieval of all the required data rather than leaving it subject to lazy loading. I.e., perhaps some of the Subject entities may have been loaded by EF and are present, while others are not present but will be lazy loaded on demand, which may be why you are getting this intermittent behavior. Doing a .AsEnumerable() forces loading of all the results for the Subject Entities from the database. Of course, this may incur a performance overhead that is not acceptable for your use case.

Update

Also wondering if data.ClientId could be part of the problem. data is not in the sample you posted.

Update 2 As @TravisJ implies in comments below, it is unlikely that my answer is correct or fully correct for @acullen72's situation. However, @acullen72 is asking that the post stay up for now.

DWright
  • 9,258
  • 4
  • 36
  • 53
  • Any enumeration of the resulting query should also enumerate the subjects enumerable. I am not sure this would solve the "intermittent" observation. – Travis J May 11 '15 at 21:33
  • @TravisJ, good point. acullen72 are you enumerating the results, or are you examining un-enumerated results with some objects present, because EF's dbContext already has them? – DWright May 11 '15 at 21:35
  • data.ClientId is a parameter passed to the method. Also, adding "ToList()" got me the following error: {"LINQ to Entities does not recognize the method 'System.Collections.Generic.List`1[<>f__AnonymousType0`3[System.String,System.Int32,System.String]] ToList[<>f__AnonymousType0`3](System.Collections.Generic.IEnumerable`1[<>f__AnonymousType0`3[System.String,System.Int32,System.String]])' method, and this method cannot be translated into a store expression."} – acullen72 May 11 '15 at 21:42
  • @acullen72, going to delete my answer, as it is not the answer. However, before I do, just wanted to ask what happens when you do a var list = newQuery.ToList(); – DWright May 11 '15 at 21:44
  • I'd ask you to leave this here, as it asks some valuable questions. Checking the ToList() at the end of the query now. It runs, but I haven't confirmed yet if the problem still exists... – acullen72 May 11 '15 at 21:54
  • Ok, will leave here for now. – DWright May 11 '15 at 22:06
  • BTW, I switched to AsEnumerable() instead of ToList(). Found this: http://stackoverflow.com/a/792859/49251 – DWright May 11 '15 at 22:17
  • Made another edit to change suggested position of AsEnumerable. – DWright May 11 '15 at 22:26