With to create an eager-loaded join query between two tables which have a foreign key relationship (or equivalent) between each other. When cast to the corresponding POCOs, enumerable properties representing the child tables will be hydrated correctly.
There are two ways to form an
- You can specify the child table name as a named parameter.
- You can specify the child table name as part of the method
public SimpleQuery WithtargetTableName( ) public SimpleQuery With( ObjectReference targetTableReference [, out dynamic aliasedTargetTable] )
stringThe name or alias of the child table in the join
A reference to the child table in the join written using either dot notation (db.PrimaryTable.ChildTable) or index notation (db["PrimaryTable"]["SecondaryTable"]). Note the chained style of identifying the child table.
Alternately, the results of a call to
An out parameter which allows you to alias tables in the join and reuse them in later joins.
SimpleQuery object containing a With clause.
||With has been called directly on a table reference without a base command (All, FindAllBy etc)|
- or -
||There is no foreign key relationship between the primary table and the target table.|
Note that issues #282 - #284 are still open with regards to exceptions thrown by With.
Note that you cannot currently use With to eager-load grandchild tables or deeper.
With Queries Returning A SimpleRecord
If you are using a base command that returns a SimpleRecord (
FindBy), you’ll need to write the
With command before the base command. For example:
db.Albums.WithArtists().Get(1); // fluid style db.Albums.With(db.Albums.Artists).Get(1); // named parameter style
With Queries Returning A SimpleQuery
If you are using a base command that returns a SimpleQuery (
FindAllBy), you can write the
With command before or (more readably) after the base command. For example:
db.Albums.All().WithArtists(); // fluid style db.Albums.All().With(db.Albums.Artists); // named parameter style
Using With and As to Give Collection The Correct Name
If the POCO to which you are casting the results of a query uses a different property name for a table which you are eager-loading, use the
As function to change its name.
You can also use the optional out variable here to keep your Select statements compact.
dynamic BandAlias; var albumDetails = db.Albums.All() .With(db.Albums.Artists.As("BandInfo"), out BandAlias) .Select( db.Albums.Title, BandAlias.Name);
Using With and Join.On To Specify Explicit Joins
In cases where you want to eager load some data but need to specify a Join relationship explicitly, you can use
Join().On() in conjunction with
dynamic homeTeam; dynamic awayTeam; db.Fixtures.FindAllByDate(date) .Join(db.Team.As("HomeTeam"), out homeTeam) .On(db.Fixtures.HomeTeamId == homeTeam.Id) .Join(db.Team.As("AwayTeam"), out awayTeam) .On(db.Fixtures.AwayTeamId == awayTeam.Id) .With(homeTeam) .With(awayTeam);