Query with Scalar Result

This topic demonstrates creating a method in Entity Developer NHibernate model, which will execute a parameterized query returning the number of table records that satisfy the query condition.

For this, perform the following steps:

1.	In the Model Explorer context menu select the Add submenu and then select New Method, or click the corresponding button on the Model Explorer toolbar or on the main application toolbar.

2.	In the Method Editor dialog specify the SQL for the method, set the return type and customize the parameters:

EFCore_QueryWithScalarResult_General

3.	Customize the parameters:

EFCore_QueryWithScalarResult_Parameters

Click OK.

The method is created.

As a result of the code generation for the model, the following method is generated:

C#:

public System.Nullable<int> GetNumberOfDepartmentsByLocation (string Loc)

{

System.Nullable<int> result;

DbConnection connection = this.Database.GetDbConnection();

bool needClose = false;

if (connection.State != ConnectionState.Open)

{

connection.Open();

needClose = true;

}

try

{

using (DbCommand cmd = connection.CreateCommand())

{

if (this.Database.GetCommandTimeout().HasValue)

cmd.CommandTimeout = this.Database.GetCommandTimeout().Value;

cmd.CommandType = CommandType.Text;

cmd.CommandText = @"SELECT COUNT(*) AS return_value FROM Department WHERE Loc = :Loc";

DbParameter LocParameter = cmd.CreateParameter();

LocParameter.ParameterName = "Loc";

LocParameter.Direction = ParameterDirection.Input;

if (Loc != null)

{

LocParameter.Value = Loc;

}

else

{

LocParameter.DbType = DbType.String;

LocParameter.Size = -1;

LocParameter.Value = DBNull.Value;

}

cmd.Parameters.Add(LocParameter);

DbParameter returnValueParameter = cmd.CreateParameter();

returnValueParameter.Direction = ParameterDirection.ReturnValue;

returnValueParameter.DbType = DbType.Int32;

returnValueParameter.Size = -1;

cmd.Parameters.Add(returnValueParameter);

cmd.ExecuteNonQuery();

result = (System.Nullable<int>)returnValueParameter.Value;

}

finally

{

if (needClose)

connection.Close();

}

return result;

}

Note

The SQL query must be built in such a way, so that the scalar value it returns, would be returned as a column with the name 'return_value', i.e. the return value must have the 'return_value' alias.

Now it is possible to use this query in the application with the help of method wrapper. This allows working with the query with all possible convenience, as wrapper methods are strongly typed, are found by IntelliSense and have the corresponding signature.

Query with Scalar Result

See Also