I got the following sql statement that I want to implement with entity framework with linq (lambda expression). Here is the SQL:
select *
from tbl_ExampleStoneCatalog
join tbl_ExampleStoneCategory
on tbl_ExampleStoneCatalog.fk_ESC = tbl_ExampleStoneCategory.pk_ESC
join tbl_ExampleStones
on tbl_ExampleStoneCatalog.fk_ES = tbl_ExampleStones.pk_ES
join tbl_ExampleReviewStoneCatalog
on tbl_ExampleStones.pk_ES = tbl_ExampleReviewStoneCatalog.fk_ES
where .fk_StoneCategory = '%someParameter%'
I tried to use the .include() which brings me to this:
var res = (await this._exampleStoneCatalog.Query()
.include(esc => esc.ExampleStoneCategory)
.include(es => es.ExampleStones)
.include(es => es.ExampleStones.ExampleReviewStoneCatalog))
.Where(w => w.ExampleStones.ExampleReviewStoneCatalog.Any(
a => a.StoneCategoryID.Equals(%someParameter%)));
Unfortunately the code stated above won't deliver me the desired result. Furthermore there is a nested Where condition in it => ExampleStones.ExampleReviewStoneCatalog.StoneCategoryID
. From what I understand after some research is, that this is not solvable easily with .include().
Is there other ways to filter in nested queries using the lambda expression?
I have found a work around for this problem. The main challenge here is to filter in a nested SQL query. I could not find a solution with .include(). Especially my current work environment in which we are useing repository pattern wouldn't allow me to filter within includes like:
var res = await this._exampleStoneCatalog.Query().include(x => x.ExampleStones.ExampleReviewStoneCatalog.Where(w => w.StoneCategoryID.Equals(%SomeParameter%))).SelectAsync();
Hence I come to the following solution with using linq to sql.
My solution:
var exampleStoneCatalogEnum = await this._exampleStoneCatalog.Query().SelectAsync();
var exampleStoneCategoryEnum = await this._exampleStoneCategoryRepository.Query().SelectAsync();
var exampleStonesEnum = await this.exampleStonesRepository.Query().SelectAsync();
var exampleReviewStoneCatalogEnum = await this.exampleReviewStoneCatalogRepository.Query().SelectAsync();
var result = from exampleStoneCatalog in exampleStoneCatalogEnum
join exampleStoneCategory in exampleStoneCategoryEnum on exampleStoneCatalog.Id equals exampleStoneCategory.Id
join exampleStones in exampleStonesEnum on exampleStoneCatalog.Id equals exampleStones.Id
join exampleReviewStoneCatalog in exampleReviewStoneCatalogEnum on exampleStones.Id equals exampleReviewStoneCatalog.Id
where exampleReviewStoneCatalog.StoneCategoryID.Equals(revCategory)
select exampleStoneCatalog;
return result;
as you can see I first get the required data of each table and join them in my result including the where condition in the end. This returns the desired result.