CODEWITHSUNDEEP
c#asp.net-mvcentity-frameworklinq
Flexabust Bergson
Flexabust Bergson

Reputation: 762

Linq query returns half the results

I am making an intranet Website using asp.NET MVC5 and Entity Framework for Data Access. I have two tables Web_Documents and Web_Profiles and a relation's table Web_Profile_joint_Document. Now to retrieve the Documents I want, I have to use IDProfile of Web_Profiles table. Then using this, I can find documents in the relation's table. One problem though, the query I make returns half the results I want. In the test I am making I have 2 IDProfile, values being 120 & 124. Here is my query:

IEnumerable<Web_Documents> retrieveDocuments;
List<int> idProfiles = new List<int>();
idProfiles.Add(120);
idProfiles.Add(124);  
foreach (var item in idProfiles)
{
    var idDocument = context.WebProfils
                    .Where(c => c.IDProfil == item)
                    .SelectMany(c => c.Documents).ToList();


    if (idDocument != null)
    {
         if (retrieveDocuments == null)
         {
              retrieveDocuments = idDocument;
         }
         else
         {
              retrieveDocuments.Concat(idDocument);
         }
}

Since Web_Profiles and Web_Documents have a relation, I can access ICollection <Web_Documents> Documents which is in Web_Profiles POCO (entity). I then store results in retrieveDocuments. But this returns only the values for IDProfile=120. Any ideas why?

Thanks!

Remark All answers are good!

Upvotes: 0

Views: 303

Answers (4)

kari kalan
kari kalan

Reputation: 505

Replace Your Code

retrieveDocuments=retrieveDocuments.Concat(idDocument);

Upvotes: -1

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131728

The query as it is loads one profile at a time. It's not returning half the results, it's returing the results it was asked to return.

In SQL, you'd use an IN clause to return records that match multiple IDs, eg WHERE ID IN (1,4,7).

In LINQ, you can do the same by creating a list/array of values and using Contains :

var profiles = new[]{120,124};
var retrievedDocuments = context.WebProfils
                .Where(c => profiles.Contains(c.IDProfil))
                .SelectMany(c => c.Documents)
                .ToList();

Upvotes: 1

Vivien Sonntag
Vivien Sonntag

Reputation: 4639

Concat returns a new list. You need to either do

retrieveDocuments = retrieveDocuments.Concat(idDocument);

or 

retrieveDocuments.AddRange(idDocument);

Upvotes: 3

bubi
bubi

Reputation: 6501

You can delete the foreach and change your query in this way

var idDocument = context.WebProfils
                .Where(c => idProfiles.Contains(c.IDProfil))
                .SelectMany(c => c.Documents).ToList();


if (idDocument != null) // Not required. idDocument can't be null (but can be empty)
{
     // Now the following lines are not required
     if (retrieveDocuments == null)
     {
          retrieveDocuments = idDocument;
     }
     else
     {
          retrieveDocuments.Concat(idDocument);
     }
}

Upvotes: 2

Related Questions