Reputation: 219
dbContext
class Table1
public string Id1 { get; set; }
public string Field { get; set; }
public string Id2 { get; set; }
public virtual Table2 Table2 { get; set; }
class Table2
public string Id2 { get; set; }
public string Field { get; set; }
I need to search all records from Table1
where Table1Field
is in the other table list of values.
For example first I select all values which matches this search parameter
IQueryable<Table2> listOfvalues = from b in dbContext.Table2
where b.Field1.Contains(searchParam)
select b;
result = listOfvalues.ToList();
And now I need to write code which returns all fields from Table1
where Field is in that listOfValues
.
Upvotes: 2
Views: 27101
Reputation: 219
Data structure:
Table1
class Journal
int JournalId {get; set;}
Date JournalDate {get; set;}
string OwnerId {get; set;}
Table2
class Event
string EventId {get; set;}
string EventName {get; set;}
I found a simple solution but also meet another problem ...
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 now List and 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();
But for me is better me is better to have
var journals = DBContext.Journals.AsQuarable();
not JournalList. Is there any possibilities to get matching data not in list but like a query, because I need to filter it after that??
Upvotes: 0
Reputation: 431
using(dbContext = dbContext.CreateContext())
{
var setData = dbContext.Table1Set().Include(t => t.Table2);
}
This will work for your case.
Upvotes: 1
Reputation: 713
As you have a navigation property you just need to load the data.
for example:
using(dbContext = dbContext.CreateContext())
{
var foo = ...;
var results = dbContext.Table1Set.Where(t => t.table2.Field == foo);
}
As you are still within the context you can use lazy loading due to your virtual property. If you are no longer within the context scope when executing your check then you will have to use eager loading.
using(dbContext = dbContext.CreateContext())
{
var setData = dbContext.Table1Set().Include(t => t.Table2);
}
var foo = ...;
var results = setData.Where(t => t.Table2.Field = foo);
This can be useful when you load your data set in a different method and return only the Enumerable
collection.
for more information on loading related entities, this link will help:
https://msdn.microsoft.com/en-gb/data/jj574232.aspx
Upvotes: 2
Reputation: 2453
I suppose you are looking for all items whose particular field matches another field in your already avialable list . you could do something similar.
dbContext.Table1.Where(p => listOfvalues.Any(l => p.Field == l.Field)).ToList();
EDIT Try using Contains
var values = listOfvalues.Select (x => x.Field ).ToList();
dbContext.Table1.Where(p => values.Contains(p.Field)).ToList();
Upvotes: 6