user1477388 user1477388 - 1 day ago 4
C# Question

Ensuring a record has all relationships, not any

I have a linqpad script I am working on and it works but the problem is that it uses

.AsEnumerable()
which calls the collection into memory. There could be thousands of these some day, so I would like to keep everything deferred as much as possible.

I am trying to simply perform a check to make sure if I pass
new long[] { 2, 4 }
to the function, then any Experiences that have both IDs 2 and 4 will be returned.

Previously, I was using only the
.Contains()
but this would return Experiences that have either 2 or 4.

Is there a better way to write this code so that it would return an
IQueryable<Experience>
rather than a
List<Experience>
so I don't have to load all results into memory in order to perform the string concat?

void Main()
{
var AllExperiences = new List<_Experience>();
AllExperiences.Add(new _Experience { Id = 1, Name = "Experience 1" });
AllExperiences.Add(new _Experience { Id = 2, Name = "Experience 2" });

AllExperienceTags.Add(new _ExperienceTag { ExperienceId = 1, TagId = 2 });
AllExperienceTags.Add(new _ExperienceTag { ExperienceId = 1, TagId = 4 });
AllExperienceTags.Add(new _ExperienceTag { ExperienceId = 2, TagId = 2 });

var experiences = FilterBySelectedTags(AllExperiences, new[] { 2, 4 }.ToList());

experiences.Dump();
}

public List<_ExperienceTag> AllExperienceTags = new List<UserQuery._ExperienceTag>();

// Define other methods and classes here
public List<_Experience> FilterBySelectedTags(List<_Experience> experiences, List<int> selectedTagIds)
{
var filteredExperiencesTags = AllExperienceTags.Where(x => selectedTagIds.Contains(x.TagId));

var obj = filteredExperiencesTags.OrderBy(x => x.TagId).GroupBy(x => x.ExperienceId).AsEnumerable().Select(x => new
{
ExperienceId = x.Key,
ExpTags = string.Join(", ", x.Select(y => y.TagId))
});

var filteredTags = obj.Where(x => x.ExpTags == string.Join(", ", selectedTagIds));

// make sure all the selected tags are found in the experience, not just any
return experiences.Where(x => filteredTags.Select(y => y.ExperienceId).Contains(x.Id)).ToList();
}

public class _Experience
{
public int Id { get; set; }
public string Name { get; set; }
}

public class _ExperienceTag
{
public int ExperienceId { get; set; }
public int TagId { get; set; }
}

Answer

Experience entity should have a navigation property to ExperienceTags:

public virtual ICollection<ExperienceTag> ExperienceTags{get;set;}

If that is the case this should work:

var query= from e in Experiences
           let experienceTagIds=e.ExperiencesTags.Select(et=>et.TagId)
           where selectedTagIds.All(x=>experienceTagIds.Contains(x))
           select e;
Comments