Symptom

When using SnapObjects, due to the needs of different business logic, you may want to create many similar static models against the same table for different type of queries, which tends to make it difficult to maintain these models.

Resolution

There are several ways to address this issue. In most cases, you may find the following two approaches work well.

1.    Use the SqlSelectAttribute to define multiple result set types 

1)    Defines SqlSelectAttribute on the Model for different result set types

E.g.:

// This model class has three kinds of result sets using SqlSelectAttribute

[FromTable("Department", Schema = "dbo")]

[SqlSelect("sqlselect1", RawSelect = "DepartmentID, Name")]

[SqlSelect("sqlselect2", RawSelect = "DepartmentID, Name, Budget, StartDate")]

[SqlSelect("sqlselect3", RawSelect = "Name")]

public class Department

{

    [Key]

    public Int32 DepartmentID { get; set; }

    public String Name { get; set; }

    public Decimal Budget { get; set; }

    public DateTime StartDate { get; set; }

    public Int32? Administrator { get; set; }

}

2)    Use ModelSqlBuilder to get a SqlQueryBuider for the specified result set and execute a query against the SqlQueryBuider using SqlModelMapper or SqlExecutor.

E.g.:

// Gets the SQL Query Builder using the Select list named SqlSelect1.

// So it will only retireve two columns: DepartmentId and Name.

var sqlQueryBuilder = ModelSqlBuilder.GetBuilder< Department>(_context)

                                        .GetQueryBuilder("SqlSelect1");

 

// Uses the new SQL Query Builder and SqlModelMapper to load data.

var queryResult = _context.SqlModelMapper.Load<Department>( sqlQueryBuilder).ToList();

 

2.    Use the SqlQueryBuilder to build Where conditions dynamically

If the result sets are the same, but the query conditions are different, you can consider using the ModelSqlBuilder class to get an ISqlQueryBuilder object from the Model, build the Where conditions through the ISqlQueryBuilder object, and then use the SqlModelMapper or SqlExecutor to execute the query.

E.g.:

// Gets SQL query builder from the model class of department.

ISqlQueryBuilder queryBuilder = ModelSqlBuilder.GetBuilder<Department>(_context).QueryBuilder;

 

// Adds a SQL WHERE clause dynamically.

queryBuilder.Where("Name", SqlBinaryOperator.Equals, "'Engineering'");

 

// Uses new SQL query to load data.

var engineering = _context.SqlModelMapper.Load<Department>(queryBuilder).FirstOrDefault();

More information

SnapObjects also supports Model inheritance, so you can reuse this Model to a greater extent. Alternatively, when using SqlExecutor to retrieve a wide variety of result sets, you can also use the DynamicModel class provided by SnapObjects instead of the static Model. You can refer to the API reference for more details: https://www.appeon.com/support/documents/appeon_online_help/snapobjects/api_reference

 

4
1