Tukukkk

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.

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();