Simple.Data

With

Use 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 With method.

• You can specify the child table name as a named parameter.
• You can specify the child table name as part of the method

Syntax

public SimpleQuery WithtargetTableName(
)

public SimpleQuery With(
	ObjectReference targetTableReference
	[, out dynamic aliasedTargetTable]
)

Parameters

targetTableName

Type: string The name or alias of the child table in the join

targetTableReference

Type: ObjectReference
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 As

aliasedTargetTable

Type: dynamic
An out parameter which allows you to alias tables in the join and reuse them in later joins.

Return Value

Type: SimpleQuery
A SimpleQuery object containing a With clause.

Exceptions

Note that issues #282 - #284 are still open with regards to exceptions thrown by With.

Remarks

Note that you cannot currently use With to eager-load grandchild tables or deeper.

Examples

With Queries Returning A SimpleRecord

If you are using a base command that returns a SimpleRecord (Find, Get, 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 (All, FindAll, 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.

db.Albums.All().With(db.Albums.Artists.As("BandInfo"));

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

WithOne & WithMany

Use WithOne or WithMany to create an eager-loaded join query between two tables which do NOT have a foreign key relationship (or equivalent) between each other. When cast to the corresponding POCOs, data from the child table will be hydrated into a single, complex object or an IEnumerable collection respectively.

Syntax

public SimpleQuery WithOne(
	ObjectReference targetTableReference
	[, out dynamic aliasedTargetTable]
)

public SimpleQuery WithMany(
	ObjectReference targetTableReference
	[, out dynamic aliasedTargetTable]
)

Parameters

targetTableReference

Type: ObjectReference
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 As

aliasedTargetTable

Type: dynamic
An out parameter which allows you to alias tables in the join and reuse them in later joins.

Return Value

Type: SimpleQuery
A SimpleQuery object containing a With clause.

Exceptions

Remarks

WithOne and WithMany can also be used to create joins between tables that do have foreign key relationships between them, as well as With.

You cannot currently use WithOne or WithMany to eager-load grandchild tables or deeper.

Examples

Queries Returning A SimpleRecord

If you are using a base command that returns a SimpleRecord (Find, Get, FindBy), you’ll need to write the commands before the base command. For example:

db.Albums.WithOne(db.Albums.Genre).Get(1);
db.Artists.WithMany(db.Artists.Albums).Get(1);

Queries Returning A SimpleQuery

If you are using a base command that returns a SimpleQuery (All, FindAll, FindAllBy), you can write the command before or (more readably) after the base command. For example:

db.Albums.All().WithOne(db.Albums.Genre);
db.Artists.All().WithMany(db.Artists.Albums);

Using 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.

db.Albums.All().WithOne(db.Albums.Artists.As("BandInfo"));

You can also use the optional out variable here to keep your Select statements compact.

dynamic BandAlias;
var albumDetails = db.Albums.All()
    .WithOne(db.Albums.Artists.As("BandInfo"), out BandAlias)
    .Select(
       db.Albums.Title,
       BandAlias.Name);

Using 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 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)
    .WithOne(homeTeam)
    .WithOne(awayTeam);