Documentation - Data Access - Retrieve

On this page

Retrieve Entities
Code Samples
By Primary Key
By Unique Key
To List
Fluent Syntax
Builder Syntax
Top
Fluent Syntax
Builder Syntax
First
Fluent Syntax
Builder Syntax
First Or Default
Fluent Syntax
Builder Syntax
Single
Fluent Syntax
Builder Syntax
Single Or Default
Fluent Syntax
Builder Syntax

Retrieve Entities#

The Sharp Factory App generates an entire framework based on repositories in order to give the programmer the ability to interact with one or more databases.

To select entities from the data store you will need to call the one of the methods available on the entity's repository.

The Sharp Factory App generates one Repository per entity.

You will need an instance of the RepositoryContainer class to access your repositories.

The naming convention is RepositoryContainer.{Model Name}.{Sql Schema Name}.{Entity Name}.{Name Of The Method}

The methods available for a particular entity's repository can vary from one entity to another.

For example if the entity has a Primary Key a method called ByPK will be created for that entity.

The retrieval methods at a glance. Each one also has an async version (ByPKAsync, ToListAsync and so on).

Method	Parameters	Returns	Present
ByPK	The primary key's columns	One entity	When the entity has a primary key
ByUK, or ByUK1, ByUK2…	The unique key's columns	One entity	One method per unique key
ToList	A Query (none for every row)	A list	Always
Top	A count and a Query	A list of at most that many	Always
First	A Query	The first entity; throws when there is none	Always
FirstOrDefault	A Query	The first entity, or null	Always
Single	A Query	The only entity; throws when there is none or more than one	Always
SingleOrDefault	A Query	The only entity, or null; throws when there is more than one	Always

The methods that take a query take the query of their entity: the Customer repository's methods take a Query<CustomerProperty>.

In detail:

ByPK the parameters depend on the column or columns that comprise the Primary Key.

Returns a single entity.

Will only be present in the repository of those entities which have a primary key.

One parameter will be created for each column in the primary key. They will be named the same as the columns and have the correct C# types.

For entities with navigation properties there is also an overload that takes a NavProps collection to load related entities: see Navigation Properties.
ByUK the parameters depend on the column or columns that comprise the Unique Key.

Returns a single entity.

Will only be present in the repository of those entities which have one or more unique keys.

One method will be created for each unique key present on the particular entity. So if the entity has multiple unique keys one method will be generated for each one.

The method is named ByUK when the entity has one unique key, and ByUK1, ByUK2 and so on when it has several. Intellisense will let you know the name of the Unique Key(in SQL Server) to which the method is mapped to.

One parameter will be created for each column in the unique key. They will be named the same as the columns and have the correct C# types.
ToList takes a single parameter of type Query.

Returns a list of entities.

Will be present in all repositories.
Top takes two parameters:
- topcount of type int represents the max count of items expected in the result.
- query of type Query represents the query to send to the database.
Returns a list of entities.

Will be present in all repositories.
First takes a single parameter of type Query.

Returns a single entity.

Will be present in all repositories.

Throws an exception if no entities are found that match the query.
FirstOrDefault takes a single parameter of type Query.

Returns a single entity.

Will be present in all repositories.

If no entities are found for the query will return null.
Single takes a single parameter of type Query.

Returns a single entity.

Will be present in all repositories.

Throws an exception if no entities are found that match the query.

Throws an exception if more than one entity is found that matches the query.
SingleOrDefault takes a single parameter of type Query.

Returns a single entity.

Will be present in all repositories.

If no entities are found for the query it will return null.

Throws an exception if more than one entity is found that matches the query.

Code Samples#

The examples below read customers in each of the ways listed above: by primary key, by unique key, and with a query.

By Primary Key#

The following example shows how to perform the following pseudo-code query.

Select the "Customer where PrimaryKey equals 123":

// declare an instance of the Repository Container// remember this can be a singleton if you// declare it early on and reuse it for the// lifetime of your applicationvar repo = new RepositoryContainer();// call the ByPK method and pass the primary keyvar customer = repo.AppDb.Sales.Customer.ByPK(123);if (customer != null){    // do something with the customer}

By Unique Key#

In this example assume the Customer table has a Unique Key with two columns: First Name and Last Name

The following example shows how to perform the following pseudo-code query.

Select the "Customer where FirstName(UK Field) equals "John" and LastName(UK Field) equals "Doe"":

// declare an instance of the Repository Container// remember this can be a singleton if you// declare it early on and reuse it for the// lifetime of your applicationvar repo = new RepositoryContainer();// call the ByUK method and pass values of the unique keyvar customer = repo.AppDb.Sales.Customer.ByUK("John", "Doe");if (customer != null){    // do something with the customer}

To List#

The following example shows how to perform the following pseudo-code query.

Select all "Customers where Balance > 0 or (CreatedDate = today and (status = "new" or status = "inactive"))":

Fluent Syntax#

// create an instance of the Repository Container// remember this can be a singleton if you// declare it early on and reuse it for the// lifetime of your applicationvar repo = new RepositoryContainer();// create an instance of Query and// specify the entity's property containervar query = new Query<CustomerProperty>()    .BeginPredicate()    .Where(CustomerProperty.Balance).GreaterThan(0)    .Or((exp)=>    {        exp        .Where(CustomerProperty.CreatedDate).EqualsDate(DateTime.Now)        .Where((exp2) =>        {            exp2            .Where(CustomerProperty.Status).Equals("new")            .Or(CustomerProperty.Status).Equals("inactive");        });    })    .EndPredicate();// pass the query to one of the repository methods// in this case the ToList on the Customer repositoryvar result = repo.AppDb.Sales.Customer.ToList(query);

Builder Syntax#

// create an instance of the Repository Container// remember this can be a singleton if you// declare it early on and reuse it for the// lifetime of your applicationvar repo = new RepositoryContainer();// create an instance of Query and// specify the entity's property containervar query = new Query<CustomerProperty>()    .Builder((builder) =>    {        builder        .Predicate((predicate) =>        {            predicate            .Where(CustomerProperty.Balance).GreaterThan(0)            .Or((exp)=>            {                exp                .Where(CustomerProperty.CreatedDate).EqualsDate(DateTime.Now)                .Where((exp2) =>                {                    exp2                    .Where(CustomerProperty.Status).Equals("new")                    .Or(CustomerProperty.Status).Equals("inactive");                });            });        });    });// pass the query to one of the repository methods// in this case the ToList on the Customer repositoryvar result = repo.AppDb.Sales.Customer.ToList(query);

Top#

The following example shows how to perform the following pseudo-code query.

Select the top 3 "Customers where Balance > 0 or (CreatedDate = today and (status = "new" or status = "inactive"))":

Fluent Syntax#

// create an instance of the Repository Container// remember this can be a singleton if you// declare it early on and reuse it for the// lifetime of your applicationvar repo = new RepositoryContainer();// create an instance of Query and// specify the entity's property containervar query = new Query<CustomerProperty>()    .BeginPredicate()    .Where(CustomerProperty.Balance).GreaterThan(0)    .Or((exp)=>    {        exp        .Where(CustomerProperty.CreatedDate).EqualsDate(DateTime.Now)        .Where((exp2) =>        {            exp2            .Where(CustomerProperty.Status).Equals("new")            .Or(CustomerProperty.Status).Equals("inactive");        });    })    .EndPredicate();// call the Top method and pass the query instancevar result = repo.AppDb.Sales.Customer.Top(3, query);foreach (var customer in result){    // do something with each entity updated}

Builder Syntax#

// create an instance of the Repository Container// remember this can be a singleton if you// declare it early on and reuse it for the// lifetime of your applicationvar repo = new RepositoryContainer();// create an instance of Query and// specify the entity's property containervar query = new Query<CustomerProperty>()    .Builder((builder) =>    {        builder        .Predicate((predicate) =>        {            predicate            .Where(CustomerProperty.Balance).GreaterThan(0)            .Or((exp)=>            {                exp                .Where(CustomerProperty.CreatedDate).EqualsDate(DateTime.Now)                .Where((exp2) =>                {                    exp2                    .Where(CustomerProperty.Status).Equals("new")                    .Or(CustomerProperty.Status).Equals("inactive");                });            });        });    });// call the Top method and pass the query instancevar result = repo.AppDb.Sales.Customer.Top(3, query);foreach (var customer in result){    // do something with each entity updated}

First#

Throws an exception if none are found.

The following example shows how to perform the following pseudo-code query.

Select the first "Customer where Balance > 0 or (CreatedDate = today and (status = "new" or status = "inactive"))":

Fluent Syntax#

// create an instance of the Repository Container// remember this can be a singleton if you// declare it early on and reuse it for the// lifetime of your applicationvar repo = new RepositoryContainer();// create an instance of Query and// specify the entity's property containervar query = new Query<CustomerProperty>()    .BeginPredicate()    .Where(CustomerProperty.Balance).GreaterThan(0)    .Or((exp)=>    {        exp        .Where(CustomerProperty.CreatedDate).EqualsDate(DateTime.Now)        .Where((exp2) =>        {            exp2            .Where(CustomerProperty.Status).Equals("new")            .Or(CustomerProperty.Status).Equals("inactive");        });    })    .EndPredicate();try{    // call the First method and pass the query instance    var customer = repo.AppDb.Sales.Customer.First(query);    // do something with the customer}catch(InvalidOperationException ex){    // no customers where found}

Builder Syntax#

// create an instance of the Repository Container// remember this can be a singleton if you// declare it early on and reuse it for the// lifetime of your applicationvar repo = new RepositoryContainer();// create an instance of Query and// specify the entity's property containervar query = new Query<CustomerProperty>()    .Builder((builder) =>    {        builder        .Predicate((predicate) =>        {            predicate            .Where(CustomerProperty.Balance).GreaterThan(0)            .Or((exp)=>            {                exp                .Where(CustomerProperty.CreatedDate).EqualsDate(DateTime.Now)                .Where((exp2) =>                {                    exp2                    .Where(CustomerProperty.Status).Equals("new")                    .Or(CustomerProperty.Status).Equals("inactive");                });            });        });    });try{    // call the First method and pass the query instance    var customer = repo.AppDb.Sales.Customer.First(query);    // do something with the customer}catch(InvalidOperationException ex){    // no customers where found}

First Or Default#

Returns null if none are found.

The following example shows how to perform the following pseudo-code query.

Select the first "Customer where Balance > 0 or (CreatedDate = today and (status = "new" or status = "inactive"))":

Fluent Syntax#

// create an instance of the Repository Container// remember this can be a singleton if you// declare it early on and reuse it for the// lifetime of your applicationvar repo = new RepositoryContainer();// create an instance of Query and// specify the entity's property containervar query = new Query<CustomerProperty>()    .BeginPredicate()    .Where(CustomerProperty.Balance).GreaterThan(0)    .Or((exp)=>    {        exp        .Where(CustomerProperty.CreatedDate).EqualsDate(DateTime.Now)        .Where((exp2) =>        {            exp2            .Where(CustomerProperty.Status).Equals("new")            .Or(CustomerProperty.Status).Equals("inactive");        });    })    .EndPredicate();// call the FirstOrDefault method and pass the query instancevar customer = repo.AppDb.Sales.Customer.FirstOrDefault(query);if (customer != null){    // do something with the customer}

Builder Syntax#

// create an instance of the Repository Container// remember this can be a singleton if you// declare it early on and reuse it for the// lifetime of your applicationvar repo = new RepositoryContainer();// create an instance of Query and// specify the entity's property containervar query = new Query<CustomerProperty>()    .Builder((builder) =>    {        builder        .Predicate((predicate) =>        {            predicate            .Where(CustomerProperty.Balance).GreaterThan(0)            .Or((exp)=>            {                exp                .Where(CustomerProperty.CreatedDate).EqualsDate(DateTime.Now)                .Where((exp2) =>                {                    exp2                    .Where(CustomerProperty.Status).Equals("new")                    .Or(CustomerProperty.Status).Equals("inactive");                });            });        });    });// call the FirstOrDefault method and pass the query instancevar customer = repo.AppDb.Sales.Customer.FirstOrDefault(query);if (customer != null){    // do something with the customer}

Single#

Throws an exception if none are found.

Throws an exception if more than one is found.

The following example shows how to perform the following pseudo-code query.

Select a single "Customer where Balance > 0 or (CreatedDate = today and (status = "new" or status = "inactive"))":

Fluent Syntax#

// create an instance of the Repository Container// remember this can be a singleton if you// declare it early on and reuse it for the// lifetime of your applicationvar repo = new RepositoryContainer();// create an instance of Query and// specify the entity's property containervar query = new Query<CustomerProperty>()    .BeginPredicate()    .Where(CustomerProperty.Balance).GreaterThan(0)    .Or((exp)=>    {        exp        .Where(CustomerProperty.CreatedDate).EqualsDate(DateTime.Now)        .Where((exp2) =>        {            exp2            .Where(CustomerProperty.Status).Equals("new")            .Or(CustomerProperty.Status).Equals("inactive");        });    })    .EndPredicate();try{    // call the Single method and pass the query instance    var customer = repo.AppDb.Sales.Customer.Single(query);    // do something with the customer}catch(InvalidOperationException ex){    // no customers where found    // or more than one was found}

Builder Syntax#

// create an instance of the Repository Container// remember this can be a singleton if you// declare it early on and reuse it for the// lifetime of your applicationvar repo = new RepositoryContainer();// create an instance of Query and// specify the entity's property containervar query = new Query<CustomerProperty>()    .Builder((builder) =>    {        builder        .Predicate((predicate) =>        {            predicate            .Where(CustomerProperty.Balance).GreaterThan(0)            .Or((exp)=>            {                exp                .Where(CustomerProperty.CreatedDate).EqualsDate(DateTime.Now)                .Where((exp2) =>                {                    exp2                    .Where(CustomerProperty.Status).Equals("new")                    .Or(CustomerProperty.Status).Equals("inactive");                });            });        });    });try{    // call the Single method and pass the query instance    var customer = repo.AppDb.Sales.Customer.Single(query);    // do something with the customer}catch(InvalidOperationException ex){    // no customers where found    // or more than one was found}

Single Or Default#

Return null if none are found.

Throws an exception if more than one is found.

The following example shows how to perform the following pseudo-code query.

Select a single "Customer where Balance > 0 or (CreatedDate = today and (status = "new" or status = "inactive"))":

Fluent Syntax#

// create an instance of the Repository Container// remember this can be a singleton if you// declare it early on and reuse it for the// lifetime of your applicationvar repo = new RepositoryContainer();// create an instance of Query and// specify the entity's property containervar query = new Query<CustomerProperty>()    .BeginPredicate()    .Where(CustomerProperty.Balance).GreaterThan(0)    .Or((exp)=>    {        exp        .Where(CustomerProperty.CreatedDate).EqualsDate(DateTime.Now)        .Where((exp2) =>        {            exp2            .Where(CustomerProperty.Status).Equals("new")            .Or(CustomerProperty.Status).Equals("inactive");        });    })    .EndPredicate();try{    // call the SingleOrDefault method and pass the query instance    var customer = repo.AppDb.Sales.Customer.SingleOrDefault(query);    if (customer != null)    {        // do something with the customer    }}catch(InvalidOperationException ex){    // more than one was found}

Builder Syntax#

// create an instance of the Repository Container// remember this can be a singleton if you// declare it early on and reuse it for the// lifetime of your applicationvar repo = new RepositoryContainer();// create an instance of Query and// specify the entity's property containervar query = new Query<CustomerProperty>()    .Builder((builder) =>    {        builder        .Predicate((predicate) =>        {            predicate            .Where(CustomerProperty.Balance).GreaterThan(0)            .Or((exp)=>            {                exp                .Where(CustomerProperty.CreatedDate).EqualsDate(DateTime.Now)                .Where((exp2) =>                {                    exp2                    .Where(CustomerProperty.Status).Equals("new")                    .Or(CustomerProperty.Status).Equals("inactive");                });            });        });    });try{    // call the SingleOrDefault method and pass the query instance    var customer = repo.AppDb.Sales.Customer.SingleOrDefault(query);    if (customer != null)    {        // do something with the customer    }}catch(InvalidOperationException ex){    // more than one was found}