Requirement
You wrote:
I am trying to get the car customization if it has a version that versionName contains "xyz".
"The" car Customization? Does the car have exactly one Customization with this VersionName?
- Car A has three Customizations: K, L, M
- K has one Version: "1.0"
- L has two Versions: "1.0" and "2.0"
- M has three Versions: "1.0" "1.1" "2.0"
Give me "the" Customization of Car A that has a version "1.0".
Classes
Alas you forgot to give us your classes. If you've followed the entity framework code first conventions you'll have classes like this:
class Car
{
public int Id {get; set;}
... // other properties
// Every Car has zero or more Customizations (one-to-many)
public virtual ICollection<Customization> Customizations {get; set;}
}
class Customization
{
public int Id {get; set;}
... // other properties
// Every Customization belongs to exactly one Car, using foreign key
public int CarId {get; set;}
public virtual Car Car {get; set;}
// Every Customization has zero or more Versions (one-to-many)
public virtual ICollection<Version> Versions {get; set;}
}
class Version
{
public int Id {get; set;}
public string Name {get; set;}
... // other properties
// Every Version belongs to exactly one Customization, using foreign key
public int CustomizationId {get; set;}
public virtual Customization Customization {get; set;}
}
This is all that entity framework needs to detect the one-to-many relations.
In entity framework the columns of the tables are represented using non-virtual properties; the virtual properties represent the relations between the tables (one-to-many, many-to-many, ...)
Hence a foreign key is non-virtual; the object that the foreign key refers to is virtual.
Back to your question
Requirement: Given a car
and a versionName
, give me all Customizations
of this Car that has at least one Version
with a Name
equal to this VersionName
Car car = ...
string versionName = ...
You can start at Customizations or at Versions:
Start at Customizations: Give me the Customizations of my Car that has at least one Version with a Name equal to versionName:
var result = dbContext.Customizations
.Where(customization =>
// it is one of the customization of my car:
customization.CarId == car.Id
// and this customization has at least one Version with versionName:
customization.Versions.Where(version => version.Name == versionName))
.Any());
You can also start at Versions:
From every Version that has a Name equal to versionName and a Customization of my car, give me some properties of the Customization:
var result = dbContext.Versions
// keep only the Versions that have the correct VersionName
// and that is a Customization of my car:
.Where(version => version.Name == versionName
&& version.Customization.CarId == car.Id)
// from the remaining Versions Select the properties that you plan to use
.Select(version => new
{
// Select the Customization properties that you want:
Id = version.Customization.Id,
CustomizationCode = version.Customization.Code,
...
});