Entity Framework Core - Mapping Raw Query(DTO Class without ID field) , Scalar

Question

I am using EFCore with ASPNETCore, everything looks great, but I find these two situations:

I have created a DTO classes that are not tables in the database, these classes that will map a raw SQL query or procedure.

So I need to map the results of an SQL procedure in a list classs DTO, the question I have is if EFCore requires that all classes of DTO have a field named ID?, for more than my procedure does not necessarily have that field ?, there a way to specify to EFCore that my query need not map any field named ID?.

Likewise, what it is the correct way to map a scalar value or a unique result of a raw SQL query or procedure?

Thank you very much for your support.

Look at [the old answer](http://stackoverflow.com/a/34764698/315935), where I show how to work with raw SQL query and STORED PROCEDURES *without* defining of any `DbSet`. Instead of that one can use `context.Database.GetDbConnection().CreateCommand()` and to work directly with `DbCommand`. It's a matter of taste of cause, but I personally like to use the way. — Oleg, Aug 31 '16 at 21:03
Please copy & paste code, because 1. Images are harder to read, because they shrink 2. It makes it easier to use pieces of code in an answer, if necessary. — Gert Arnold, Aug 31 '16 at 22:25

score 0 · Answer 1 · answered Sep 30 '16 at 18:56

Here is a class that will solve your needs.

Usage var listOfEntity = DatabaseServices.ExecuteStoredProcedure(Stored Procedure Name, List of SqlParameter)

public class DatabaseServices : IDatabaseServices { private readonly ApplicationDbContext _applicationDbContext;

    public DatabaseServices(ApplicationDbContext applicationDbContext)
    {
        _applicationDbContext = applicationDbContext;
    }

    public List<T> ExecuteStoreProcedure<T>(string storedProcedure, List<SqlParameter> parameters) where T : new()
    {
        using (var cmd = _applicationDbContext.Database.GetDbConnection().CreateCommand())
        {
            cmd.CommandText = storedProcedure;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 300;

            // set some parameters of the stored procedure
            foreach (var parameter in parameters)
            {
                parameter.Value = parameter.Value ?? DBNull.Value;
                cmd.Parameters.Add(parameter);
            }

            if (cmd.Connection.State != ConnectionState.Open)
                cmd.Connection.Open();

            using (var dataReader = cmd.ExecuteReader())
            {
                var test = DataReaderMapToList<T>(dataReader);
                return test;
            }
        }
    }

    private static List<T> DataReaderMapToList<T>(DbDataReader dr)
    {
        List<T> list = new List<T>();

        if (dr.HasRows)
        {
            while (dr.Read())
            {
                var obj = Activator.CreateInstance<T>();
                foreach (PropertyInfo prop in obj.GetType().GetProperties())
                {
                    if (!Equals(dr[prop.Name], DBNull.Value))
                    {
                        prop.SetValue(obj, dr[prop.Name], null);
                    }
                }
                list.Add(obj);
            }
            return list;
        }
        return new List<T>();
    }
}

Entity Framework Core - Mapping Raw Query(DTO Class without ID field) , Scalar

1 Answers1