Custom select items in Query using Dapper dapper

Question

I am new in Dapper. I want to select dynamic items from conn.Query<>() that it execute a stored procedure in SQL.

My code is similar to :

            connection.Open();
            var parameters = new DynamicParameters();
            parameters.Add("@name", x.GetValue<string>("Name"));
            parameters.Add("@lastname", x.GetValue<string>("LastName"));
            var query = connection.Query<Costumer>(sql: "GetCostumer", param: parameters, commandType: CommandType.StoredProcedure);

and my stored procedure is similar to:

select * from Costumer where name = @Name and lastname = @lastname

In entity framework i can resolve my problem by this code:

IQueryable<User> query = container.GetQuery<Users>();
var finalResult = (from item in query
              select new {
                  item.name,
                  item.lastname
              }).ToList();

Accepted Answer

If the question is about selecting just the two columns, then at the simplest level, you can just do exactly what you are already doing, i.e.

var finalResult = (from item in query
                  select new {
                      item.Name,
                      item.LastName
                  }).ToList();

Note that this does fetch all the columns back to the client, and then re-maps there. If you want to do the column filtering at the server, then you'll need to use something like INSERT EXEC with a subsequent SELECT, i.e.

DECLARE @table_var TABLE (cols)
INSERT INTO @table_var (cols) EXEC proc {args}
SELECT foo, bar FROM @table_var

Although a cleaner implementation might be to expose a UDF - then you can just use:

SELECT foo, bar FROM dbo.SomeFunction(args);

As an aside, if the <Costumer> type doesn't already exist, there is a way to avoid the intermediate type via dynamic and the non-generic Query method, but it is actually slightly less efficient than the above, so should be used sparingly.

Note also that you don't need DynamicParameters in this case; you could just use:

var parameters = new {
    name = x.GetValue<string>("Name"),
    lastname = x.GetValue<string>("LastName")
};
var query = connection.Query<Costumer>("GetCostumer", parameters,
    commandType: CommandType.StoredProcedure);


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why