Entity Framework query Related Entities in single query
I have hierarchical data with 4 levels of parent child data. CropCategory has many Crops which has many SubCrops which in turn has many Cultivars. Each Entity has an Id and a Description where the descriptions are not unique in the single entities but are as a combination of all 4.
I need to identify a single Cultivar from a set of Descriptions. I have the following code which works fine but I am positive that it is not the most efficient way to get to the answer. I would like to do it in a single round trip to the database.
string cropCategoryDescription = "Some Crop Category"
string cropDescription = "Some Crop"
string subCropDescription = "Some SubCrop"
string cultivarDescription = "Some Cultivar"
CropCategory cropCategory = _context.CropCategories.FirstOrDefault(cc => cc.Description == cropCategoryDescription);
Crop crop = _context.Crops.FirstOrDefault(c => c.Description == cropDescription && c.CropCategoryId == cropCategory.CropCategoryId);
SubCrop subCrop = _context.SubCrops.FirstOrDefault(sc => sc.Description == subCropDescription && sc.CropId == crop.CropId);
Cultivar cultivar = _context.Cultivars.FirstOrDefault(cu => cu.Description == cultivarDescription && cu.SubCropId == subCrop.SubCropId);
CurrentCultivar = cultivar;
I have read quite a number of posts and blogs in search of the theory but am getting confused with the various terminologies. Any ideas to point me in the right direction would be appreciated.
linq lambda entity-framework-6
asked Nov 19 '18 at 21:09
TonyTony
1314
add a comment |
I have hierarchical data with 4 levels of parent child data. CropCategory has many Crops which has many SubCrops which in turn has many Cultivars. Each Entity has an Id and a Description where the descriptions are not unique in the single entities but are as a combination of all 4.
I need to identify a single Cultivar from a set of Descriptions. I have the following code which works fine but I am positive that it is not the most efficient way to get to the answer. I would like to do it in a single round trip to the database.
string cropCategoryDescription = "Some Crop Category"
string cropDescription = "Some Crop"
string subCropDescription = "Some SubCrop"
string cultivarDescription = "Some Cultivar"
CropCategory cropCategory = _context.CropCategories.FirstOrDefault(cc => cc.Description == cropCategoryDescription);
Crop crop = _context.Crops.FirstOrDefault(c => c.Description == cropDescription && c.CropCategoryId == cropCategory.CropCategoryId);
SubCrop subCrop = _context.SubCrops.FirstOrDefault(sc => sc.Description == subCropDescription && sc.CropId == crop.CropId);
Cultivar cultivar = _context.Cultivars.FirstOrDefault(cu => cu.Description == cultivarDescription && cu.SubCropId == subCrop.SubCropId);
CurrentCultivar = cultivar;
I have read quite a number of posts and blogs in search of the theory but am getting confused with the various terminologies. Any ideas to point me in the right direction would be appreciated.
linq lambda entity-framework-6
asked Nov 19 '18 at 21:09
TonyTony
1314
add a comment |
I have hierarchical data with 4 levels of parent child data. CropCategory has many Crops which has many SubCrops which in turn has many Cultivars. Each Entity has an Id and a Description where the descriptions are not unique in the single entities but are as a combination of all 4.
I need to identify a single Cultivar from a set of Descriptions. I have the following code which works fine but I am positive that it is not the most efficient way to get to the answer. I would like to do it in a single round trip to the database.
string cropCategoryDescription = "Some Crop Category"
string cropDescription = "Some Crop"
string subCropDescription = "Some SubCrop"
string cultivarDescription = "Some Cultivar"
CropCategory cropCategory = _context.CropCategories.FirstOrDefault(cc => cc.Description == cropCategoryDescription);
Crop crop = _context.Crops.FirstOrDefault(c => c.Description == cropDescription && c.CropCategoryId == cropCategory.CropCategoryId);
SubCrop subCrop = _context.SubCrops.FirstOrDefault(sc => sc.Description == subCropDescription && sc.CropId == crop.CropId);
Cultivar cultivar = _context.Cultivars.FirstOrDefault(cu => cu.Description == cultivarDescription && cu.SubCropId == subCrop.SubCropId);
CurrentCultivar = cultivar;
I have read quite a number of posts and blogs in search of the theory but am getting confused with the various terminologies. Any ideas to point me in the right direction would be appreciated.
linq lambda entity-framework-6
asked Nov 19 '18 at 21:09
TonyTony
1314
I have hierarchical data with 4 levels of parent child data. CropCategory has many Crops which has many SubCrops which in turn has many Cultivars. Each Entity has an Id and a Description where the descriptions are not unique in the single entities but are as a combination of all 4.
I need to identify a single Cultivar from a set of Descriptions. I have the following code which works fine but I am positive that it is not the most efficient way to get to the answer. I would like to do it in a single round trip to the database.
string cropCategoryDescription = "Some Crop Category"
string cropDescription = "Some Crop"
string subCropDescription = "Some SubCrop"
string cultivarDescription = "Some Cultivar"
CropCategory cropCategory = _context.CropCategories.FirstOrDefault(cc => cc.Description == cropCategoryDescription);
Crop crop = _context.Crops.FirstOrDefault(c => c.Description == cropDescription && c.CropCategoryId == cropCategory.CropCategoryId);
SubCrop subCrop = _context.SubCrops.FirstOrDefault(sc => sc.Description == subCropDescription && sc.CropId == crop.CropId);
Cultivar cultivar = _context.Cultivars.FirstOrDefault(cu => cu.Description == cultivarDescription && cu.SubCropId == subCrop.SubCropId);
CurrentCultivar = cultivar;
I have read quite a number of posts and blogs in search of the theory but am getting confused with the various terminologies. Any ideas to point me in the right direction would be appreciated.
linq lambda entity-framework-6
linq lambda entity-framework-6
asked Nov 19 '18 at 21:09
TonyTony
1314
asked Nov 19 '18 at 21:09
TonyTony
1314
asked Nov 19 '18 at 21:09
TonyTony
1314
asked Nov 19 '18 at 21:09
TonyTony
1314
asked Nov 19 '18 at 21:09
TonyTony
1314
1314
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Use the navigation properties that should be defined in your models:
var result = _context.Cultivars
.Where( cu =>
cu.Description == cultivarDescription
&& cu.SubCrop.Description == subCropDescription
&& cu.SubCrop.Crop.Description == cropDescription
&& cu.SubCrop.Crop.CropCategory.Description == cropCategoryDescription )
// projection to match your results
// but you could use Include()'s on _context.Cultivars
.Select( cu => new
{
Cultivar = cu,
SubCrop = cu.SubCrop,
Crop = cu.SubCrop.Crop,
CropCategory = cu.SubCrop.Crop.CropCategory,
})
.FirstOrDefault();
answered Nov 19 '18 at 22:08
MohoMoho
11.1k11723
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53382670%2fentity-framework-query-related-entities-in-single-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Use the navigation properties that should be defined in your models:
var result = _context.Cultivars
.Where( cu =>
cu.Description == cultivarDescription
&& cu.SubCrop.Description == subCropDescription
&& cu.SubCrop.Crop.Description == cropDescription
&& cu.SubCrop.Crop.CropCategory.Description == cropCategoryDescription )
// projection to match your results
// but you could use Include()'s on _context.Cultivars
.Select( cu => new
{
Cultivar = cu,
SubCrop = cu.SubCrop,
Crop = cu.SubCrop.Crop,
CropCategory = cu.SubCrop.Crop.CropCategory,
})
.FirstOrDefault();
answered Nov 19 '18 at 22:08
MohoMoho
11.1k11723
add a comment |
Use the navigation properties that should be defined in your models:
var result = _context.Cultivars
.Where( cu =>
cu.Description == cultivarDescription
&& cu.SubCrop.Description == subCropDescription
&& cu.SubCrop.Crop.Description == cropDescription
&& cu.SubCrop.Crop.CropCategory.Description == cropCategoryDescription )
// projection to match your results
// but you could use Include()'s on _context.Cultivars
.Select( cu => new
{
Cultivar = cu,
SubCrop = cu.SubCrop,
Crop = cu.SubCrop.Crop,
CropCategory = cu.SubCrop.Crop.CropCategory,
})
.FirstOrDefault();
answered Nov 19 '18 at 22:08
MohoMoho
11.1k11723
add a comment |
Use the navigation properties that should be defined in your models:
var result = _context.Cultivars
.Where( cu =>
cu.Description == cultivarDescription
&& cu.SubCrop.Description == subCropDescription
&& cu.SubCrop.Crop.Description == cropDescription
&& cu.SubCrop.Crop.CropCategory.Description == cropCategoryDescription )
// projection to match your results
// but you could use Include()'s on _context.Cultivars
.Select( cu => new
{
Cultivar = cu,
SubCrop = cu.SubCrop,
Crop = cu.SubCrop.Crop,
CropCategory = cu.SubCrop.Crop.CropCategory,
})
.FirstOrDefault();
answered Nov 19 '18 at 22:08
MohoMoho
11.1k11723
Use the navigation properties that should be defined in your models:
var result = _context.Cultivars
.Where( cu =>
cu.Description == cultivarDescription
&& cu.SubCrop.Description == subCropDescription
&& cu.SubCrop.Crop.Description == cropDescription
&& cu.SubCrop.Crop.CropCategory.Description == cropCategoryDescription )
// projection to match your results
// but you could use Include()'s on _context.Cultivars
.Select( cu => new
{
Cultivar = cu,
SubCrop = cu.SubCrop,
Crop = cu.SubCrop.Crop,
CropCategory = cu.SubCrop.Crop.CropCategory,
})
.FirstOrDefault();
answered Nov 19 '18 at 22:08
MohoMoho
11.1k11723
edited Nov 19 '18 at 22:30
answered Nov 19 '18 at 22:08
MohoMoho
11.1k11723
answered Nov 19 '18 at 22:08
MohoMoho
11.1k11723
answered Nov 19 '18 at 22:08
MohoMoho
11.1k11723
11.1k11723
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53382670%2fentity-framework-query-related-entities-in-single-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
