How to map user defined type on sql server with EF fluent api to a model?

Question

On sql server i have made user defined type like this

CREATE TYPE [TowarType] AS TABLE(
    [GIDTyp] [int] NULL,
    [GIDNumer] [int] NULL,
    [Kod] [varchar](255) NULL
)

in c# its represented by a model

public class TowaryType
{
    public int GIDTyp { get; set; }
    public int GIDNumer { get; set; }
    public string Kod { get; set; }
}

i also have a stored procedure that takes this type as a parameter, this procedure returns a sql query result table, i want to call this stored procedure with entity framework and retrieve the result. Im doing it like this

   public List<ReturnModelType> GetResultFromSqlQuery<ReturnModelType>(string sqlQuery, params object[] parameters)
    {
        var result = this.Database.SqlQuery(typeof(ReturnModelType), sqlQuery, parameters);
        List<ReturnModelType> localModels = new List<ReturnModelType>();
        foreach (var o in result)
        {

            ReturnModelType m = (ReturnModelType)o;
            localModels.Add(m);
        }
        return localModels;
    }

//

 var result = _context.GetResultFromSqlQuery<ResultModel>(
                        "exec StanTowarow @Magazyny, @Towary, @Dzien", 
                        new SqlParameter("@Magazyny", magazynyParams),
                        new SqlParameter("@Towary", towaryParams),
                        new SqlParameter("@Dzien", dzien)

                    );

but i get System.ArgumentException which says that no map exists for my user defined type.

I think you should map it with fluent API but i dont know how, could anyone point me into right direction?

Answer 1

I have managed to fix my problem after some reading. My problem was that properly configured SqlParameter objects were needed that would properly pass my Table Valued User Defined Parameters. I Created a function that consumed list of objects from my model and returned a properly configured SqlParameter.

Here is my user defined parameter

CREATE TYPE [TowarType] AS TABLE(
    [GIDTyp] [int] NULL,
    [GIDNumer] [int] NULL,
    [Kod] [varchar](255) NULL
)

// Here is a c# model of this parameter

 public class TowaryType
{
    public int GIDTyp { get; set; }
    public int GIDNumer { get; set; }
    public string Kod { get; set; }
}

I create a list of my model and i populate it with data

List<TowaryType> modelList = new List<TowaryType>();
//populating code here

After that i need to create a SqlParameter out of that list, so i made a method in my applicationDbContext that does that for me:

public SqlParameter GetTableValuedParameter<Model>(List<Model> valueTable, string typeName, string paramName)
        {
            if(valueTable == null || valueTable.Count == 0)
                throw new Exception("Cant be empty or null.");

            DataTable dataTable = new DataTable();

            var header = valueTable.First().GetType().GetProperties();

            foreach (var propertyInfo in header)
            {
                dataTable.Columns.Add(propertyInfo.Name);
            }

            foreach (var model in valueTable)
            {
                DataRow dataRow = dataTable.NewRow();
                var properties = model.GetType().GetProperties();
                foreach (var propertyInfo in properties)
                {
                    dataRow[propertyInfo.Name] = propertyInfo.GetValue(model);
                }

                dataTable.Rows.Add(dataRow);
            }

            var sqlParameter = new SqlParameter(paramName, SqlDbType.Structured);
            sqlParameter.Value = dataTable;
            sqlParameter.TypeName = typeName;

            return sqlParameter;
        }

After that all i had to do is call this function on my lists of models which i want to pass to my stored procedure and then just call my stored procedure

var param1 = _context.GetTableValuedParameter(magazynyParams, "MagazynType", "@Magazyny");
var param2 = _context.GetTableValuedParameter(towaryParams, "TowarType", "@Towary");

                var result = _context.GetResultFromSqlQuery<ResultModel>(
                    "exec StanTowarow @Magazyny, @Towary, @Dzien", 
                    param1,
                    param2,
                    new SqlParameter("@Dzien", dzien)