Retrieve

Table Of Contents

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 following is a list of all possible data retrieval methods that could potentially be present in an entity's repository:

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

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

    Each method would be named ByPK{number} like ByUK1, ByUK2 etc... 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.

  3. ToList takes a single parameter of type Query.

    Returns a list of entities.

    Will be present in all repositories.

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

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

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

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

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


By Primary Key

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

Select the "Customer where PrimaryKey equals 123":

C# 
// 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 application

var repo = new RepositoryContainer();

// call the ByPK method and pass the primary key

var 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"":

C# 
// 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 application

var repo = new RepositoryContainer();

// call the ByUK method and pass values of the unique key

var 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

C# 
// 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 application

var repo = new RepositoryContainer();

// create an instance of Query and 

// specify the entity's PropertyEnum

var 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 repository

var result = repo.AppDb.Sales.Customer.ToList(query);

Builder Syntax

C# 
// 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 application

var repo = new RepositoryContainer();

// create an instance of Query and 

// specify the entity's PropertyEnum

var 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 repository

var 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

C# 
// 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 application

var repo = new RepositoryContainer();

// create an instance of Query and 

// specify the entity's PropertyEnum

var 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 instance

var result = _repo.AppDb.Sales.Customer.Top(3, query);

foreach (var customer in result)
{
    // do something with each entity updated

}

Builder Syntax

C# 
// 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 application

var repo = new RepositoryContainer();

// create an instance of Query and 

// specify the entity's PropertyEnum

var 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 instance

var 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

C# 
// 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 application

var repo = new RepositoryContainer();

// create an instance of Query and 

// specify the entity's PropertyEnum

var 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

C# 
// 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 application

var repo = new RepositoryContainer();

// create an instance of Query and 

// specify the entity's PropertyEnum

var 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

C# 
// 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 application

var repo = new RepositoryContainer();

// create an instance of Query and 

// specify the entity's PropertyEnum

var 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 instance

var customer = _repo.AppDb.Sales.Customer.FirstOrDefault(query);

if (customer != null)
{
    // do something with the customer

}

Builder Syntax

C# 
// 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 application

var repo = new RepositoryContainer();

// create an instance of Query and 

// specify the entity's PropertyEnum

var 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 instance

var 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

C# 
// 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 application

var repo = new RepositoryContainer();

// create an instance of Query and 

// specify the entity's PropertyEnum

var 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

C# 
// 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 application

var repo = new RepositoryContainer();

// create an instance of Query and 

// specify the entity's PropertyEnum

var 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

C# 
// 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 application

var repo = new RepositoryContainer();

// create an instance of Query and 

// specify the entity's PropertyEnum

var 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

C# 
// 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 application

var repo = new RepositoryContainer();

// create an instance of Query and 

// specify the entity's PropertyEnum

var 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

}