Linq In Clause & Predicate building
I have two tables. Report and ReportData. ReportData has a constraint ReportID.
How can I write my linq query to return all Report objects where the predicate conditions are met for ReportData? Something like this in SQL:
SELECT * FROM Report as r
Where r.ServiceID = 3 and r.ReportID IN (Select ReportID FROM ReportData WHERE JobID LIKE 'Something%')
This is how I'm building my predicate:
Expression<Func<ReportData, bool>> predicate = PredicateBuilder.True<ReportData>();
predicate =开发者_StackOverflow社区 predicate.And(x => x.JobID.StartsWith(QueryConfig.Instance.DataStreamName));
var q = engine.GetReports(predicate, reportsDataContext);
reports = q.ToList();
This is my query construction at the moment:
public override IQueryable<Report> GetReports(Expression<Func<ReportData, bool>> predicate, LLReportsDataContext reportDC)
{
if (reportDC == null) throw new ArgumentNullException("reportDC");
var q = reportDC.ReportDatas.Where(predicate).Where(r => r.ServiceID.Equals(1)).Select(r => r.Report);
return q;
}
I've tried doing the following as well: public override IQueryable GetReports(Expression> predicate, LLReportsDataContext reportDC) { if (reportDC == null) throw new ArgumentNullException("reportDC");
var q = from r in reportDC.Reports
where r.ServiceID.Equals(1)
where r.ReportDatas.Where(predicate.Compile()).Select(x => r.ReportID).Contains(r.ReportID)
select r;
return q;
}
However, I get the this Exception: "Unsupported overload used for query operator 'Where'."
UPDATE This fixed it:
var q = reportDC.Reports.AsExpandable().
Where(r => r.ReportDatas.Any(predicate.Compile()))
.Where(r => r.ServiceID.Equals(1));
Query
ReportDatas
.Where( reportData => reportData.StartsWith( "Something%" ) &&
reportData.Report.Id ==3)
.Select( reportData => reportData.Report )
.Distinct()
AboutLinqKit
When using LinqKit, sometimes you need to call AsExpandable()
in the entity collection and to compile the predicate expression. see this example : ): how-to-use-predicate-builder-with-linq2sql-and-or-operator
精彩评论