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

c# dbcontext entity-framework entity-framework-core stored-procedures

Question

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

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

Context Here!

Class DTO

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

rawQuery

Repository

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

1
0
8/31/2016 8:29:31 PM

Popular Answer

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>();
    }
}
0
9/30/2016 6:56:33 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow