How to get IQueryable() instead of List() using LinQ?

Question

Data structure:

class Journal
{    
    public int JournalId {get; set;}
    public DateTime JournalDate {get; set;}
    public string OwnerId {get; set;}
}

class Event
{
    public string EventId {get; set;}
    public string EventName {get; set;}
}

Is there any way to get First of all I get that List of values

var events = DBContext.Events.Where(o => o.EventName.Contains(searchParam)).AsQueryable();
var values = events.Select(x => x.EventID).ToList();

I only know how to put query results to list in foreach cycle. I created new list put there all values where events in list of values I need

JournalList  = new List<Journal>();
foreach (string eventId in values)
{
   var results = DBContext.Journals.Where(j => j.eventID.Equals   (eventId)).ToList();
   RegJournalList.AddRange(results);
}

GroupedList = JournalList.Select(t => new JournalGrouped()
{
  JournalID = t.JournalID,
  JournalDate = t.JournalDate
}).Distinct().ToList();

Is there a way to have IQueryable() instead of JournalList?

Answer 1

I suggest you look into join. It is basically what you are trying to do

var result = (from event in DBContext.Events
              where event.EventName.Contains(searchParam)
              join journal in DBContext.Journals on event.EventID equals journal.JournalID
              select new JournalGrouped()
              {
                JournalID = journal.JournalID,
                JournalDate = journal.JournalDate
              }).Distinct().ToList();

Or better still since you are using Entity Framework use navigation properties and let the framework do the work for you. After defining the navigation properties it will look something like:

from journal in DBContext.Journals.Include("full name of your Event class")
where journal.Event.EventName.Contains(searchParam)
select new JournalGrouped()
{
   JournalID = journal.JournalID,
   JournalDate = journal.JournalDate
}

If you for some reason what to stick with the way you currenly are doing it then:

var values = DBContext.Events.Where(o => o.EventName.Contains(searchParam))
                             .Select(x => x.EventID);

var GroupedList = DBContext.Journals.Where(j => values.Contain(j.eventID))
                                    .Select(t => new JournalGrouped()
                                    {
                                        JournalID = t.JournalID,
                                        JournalDate = t.JournalDate
                                    }).Distinct();