Apparently you have two tables: Quotes and Materials. There seems to be a one-to-many relation between Quotes and Materials: every Quote has zero or more Materials; every Material belongs to exactly one Quote, namely the Quote that the foreign key refers to.
If you follow the entity framework conventions you'll have classes like the following:
public class Quote
{
public int Id{ get; set; }
public int Type { get; set; }
// every Quote has zero or more Materials (one-to-many)
public virtual ICollection<string> Materials { get; set; }
}
public class Material
{
public int Id{ get; set; }
... // other Material properties
// Every Material belongs to exactly one Quote, using foreign key:
public int QuoteId {get; set;}
public virtual Quote Quote {get; set;}
}
In entity framework, the columns of the tables are represented by non-virtual properties. The virtual properties represent the relations between the tables (one-to-many, many-to-many, ...).
The foreign key is a real column in the Materials table, hence QuoteId is a non-virtual property. Every Material belongs to exactly one Quote (relation), hence property Quote is a virtual property.
Note that I changed List<Material> with ICollection<Material>. The reason is that if you got a Quote x object, then x.Materials[4] has no defined meaning. Furthermore, why force entity framework to convert the fetched data into a List? If entity framework thinks that it can fetch the data in a smarter way, let it do it. Furthermore, you are not tempted to use the index of the list if this index doesn't have a defined meaning.
Fetch Quotes with (Some of their) Materials
using (var dbContext = new MyDbContext(...))
{
var quotes = dbContext.Quotes
.Where(quote => ...) // only if you don't want all Quotes
.Select(quote => new
{
// Select only the Quote properties that you actually plan to use
Id = quote.Id,
Type = quote.Type,
... // other Quote properties
Materials = quote.Materials
.Where(material => ...) // only if you don't want all Materials of this Quote
.Select(material => new
{
// Again: select only the properties that you plan to use
Id = material.Id
...
// not needed, you already know the value:
// QuoteId = material.QuoteId,
})
.ToList(),
});
Entity framework knows the relation between tables Quotes and Materials. Whenever you use one of the virtual properties, Entity framework will create the correct (Group-)Join for you.
Fetch data without using the ICollection
Some people prefer not to use virtual properties, or they use a version of entity framework that does not support this. In that case you'll have to do the (Group-)Join yourselve.
In a one-to-many relation, when starting at the one-side and fetch the many sub-items use GroupJoin. When starting at the many-side and fetching the one parent item that the foreign key refers to, use Join
So if you want to fetch Schools with their zero or more Students, Customers with their zero or more Orders, or Quotes with their Materials, use GroupJoin.
If you want to query Students, each Student with its one and only School, or Orders with their one and only Customer, or Material with its Quote, use Join
I almost always use the overload of Queryable.GroupJoin that has a parameter resultSelector to exactly specify which properties I want to query.
var quotes = dbContext.Quotes
.GroupJoin(dbContext.Materials, // GroupJoin Quotes and Materials
quote => quote.Id, // from Every Quote take the primary key
material => material.QuoteId, // from every Material take the foreign key
// parameter resultSelector: from every quote with its zero Materials, make one new
(quote, materialsOfThisQuote) => new
{
Id = quote.Id,
Type = quote.Type,
... // other Quote properties
Materials = materialsOfThisQuote
.Select(material => new
{
Id = material.Id
...
})
.ToList(),
});
In words: GroupJoin the tables of Quotes and Materials. From every quote in the table of Quotes take the primary key; from every material in the table of Materials take the foreign key to its quote. From every quote, with all its matching materials, make one new object, containing the selected properties.
By the way, did you notice, that if plural nouns are used for collections, and singular nouns for elements of the collection, that queries will be much easier to read?
Why is Select preferred above fetching complete rows?
You could also fetch the Quotes without using Select:
var result = dbContext.Quotes.Include(quote => quote.Materials);
This will fetch complete rows from the Quotes table, and complete rows from the Materials table. This will probably fetch more properties than you actually use.
Database management systems are extremely optimized for selecting data. One of the slower parts of the query is the transfer of the selected data to your local process. Hence it is wise to limit the number of transferred items as much as possible.
If you fetch Quote [10] with its 2000 Materials, then every Material of this Quote will have a foreign key QuoteId with a value of 10. You will be transferring this value over 2000 times. What a waste of processing power!
Another reason to use Select, even if you plan to Select all properties is because the DbContext has a ChangeTracker. Whenever you query complete rows, so whenever you query data without using Select, or use Include, then the fetched rows will be stored in the ChangeTracker, together with a Clone. You get the reference to the original. Whenever you change the values of the properties of the reference, you change the values in the Original.
If later you call SaveChanges, every Property of the original is compared by value with the Clone. If they differ, the items are updated in the database.
// Fetch the 2000 quotes to display, each with their Materials:
var quotesToDisplay = dbContext.Quotes.Include(Materials)
.Where(quote => quote.Type == QuoteType.Normal)
.ToList();
// Fetch the 5 quotes that must be changed
var quotesToChange = dbContext.Quotes
.Where(quote => quote.Type == QuoteType.Special)
.ToList();
this.ChangeQuote(quotesToChange);
// update the database
dbContext.SaveChanges();
The ChangeTracker will comparer every of the 2000 unchanged Quotes, value by value with their Clones, to see if they are changed. It will also check all fetched Materials. Because you know that you only fetched the data to display, this would be a huge waste of processing power.
When using entity framework always fetch data using Select and Select only the properties that you actually plan to use. Only fetch complete rows, only use Include if you plan to update the fetched data.
Having to type less source code is not a good reason to fetch complete rows.
So the following would be way more efficient:
List<Quote> QuotesToDisplay = dbContext.Quotes
.Where(quote => quote.Type == QuoteType.Normal)
.Select(quote => new Quote
{
// Select only the Quote properties that you actually plan to use
Id = quote.Id,
Type = quote.Type,
...
Materials = quote.Materials
.Where(material => ...) // only if you don't want all Materials of this Quote
.Select(material => new Material
{
Id = material.Id
...
// Not needed: QuoteId = material.QuoteId,
})
.ToList(),
})
.ToList();
This fetched data won't be in the ChangeTracker.
// Fetch the 5 quotes that must be changed
var quotesToChange = dbContext.Quotes
.Where(quote => quote.Type == QuoteType.Special)
.ToList();
this.ChangeQuote(quotesToChange);
// update the database
dbContext.SaveChanges();
Now only the 5 Quotes and their Materials will be in the ChangeTracker. If you don't plan to update these Materials, then don't use Include, to limit the items in the ChangeTracker even more.
