I have the following classes:

public class Template
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public bool SignaturePerLine { get; set; }
        public string SpecialTest { get; set; }
        public TemplateType TemplateType { get; set; } = new TemplateType();
        public int StandardTime { get; set; }
        public DateTime StartDate { get; set; }
    }

public class TemplateType
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public bool Pictures { get; set; }
        public bool Video { get; set; }
        public bool Matrix { get; set; }    
    }

My database tables are predominantly the same except my Template table has a foreign key of TemplateTypeId which stores the Id from the TemplateType table. Pretty standard stuff.

I am using Dappper to try to insert a fully populated Template object like so...

string sql = $@"insert into dbo.Template (Title, SignaturePerLine, StandardTime, StartDate, SpecialTest) 
                     values (@Title, @SignaturePerLine, @StandardTime, @StartDate, @SpecialTest);";
        
connection.Execute(sql,Template);

The issue...

My problem occurs when I try to add in the TemplateTypeId. How can I tell Dapper to insert this?

I had hoped I could do something like this:

string sql = $@"insert into dbo.Template (Title, SignaturePerLine, StandardTime, StartDate, SpecialTest, TemplateTypeId) 
      values (@Title, @SignaturePerLine, @StandardTime, @StartDate, @SpecialTest, @TemplateType.Id);";

But that does not work.

I could maybe add each property as a set of DynamicParameters but I was hoping to avoid that approach as some of my other classes have 20 properties.

Any help much appreciated.

CodePudding user response：

Usually what I do in these situations is create two sets of models. 'DataAccess' models, which are 1 to 1 with the database and 'Domain' models for end user. Furthermore create mappings from domain to dataAccess and vice-versa.

As you mentioned, having models with more 20 properties, this helps you manage the data more easly.

namespace DataAccess.Models;

public class TemplateDb
{
    public TemplateDb(int id, string title, bool signaturePerLine, string specialTest, int templateTypeId, int standardTime, DateTime startDate)
    {
        Id = id;
        Title = title ?? throw new ArgumentNullException(nameof(title));
        SignaturePerLine = signaturePerLine;
        SpecialTest = specialTest ?? throw new ArgumentNullException(nameof(specialTest));
        TemplateTypeId = templateTypeId;
        StandardTime = standardTime;
        StartDate = startDate;
    }

    public int Id { get; set; }
    public string Title { get; set; }
    public bool SignaturePerLine { get; set; }
    public string SpecialTest { get; set; }
    public int TemplateTypeId { get; set; }
    public int StandardTime { get; set; }
    public DateTime StartDate { get; set; }
}

public class TemplateTypeDb
{
    public TemplateTypeDb(int id, string name, string description, bool pictures, bool video, bool matrix)
    {
        Id = id;
        Name = name ?? throw new ArgumentNullException(nameof(name));
        Description = description ?? throw new ArgumentNullException(nameof(description));
        Pictures = pictures;
        Video = video;
        Matrix = matrix;
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public bool Pictures { get; set; }
    public bool Video { get; set; }
    public bool Matrix { get; set; }
}

namespace Domain.Models;

public class Template
{
    public Template(int id, string title, bool signaturePerLine, string specialTest, TemplateType templateType, int standardTime, DateTime startDate)
    {
        Id = id;
        Title = title ?? throw new ArgumentNullException(nameof(title));
        SignaturePerLine = signaturePerLine;
        SpecialTest = specialTest ?? throw new ArgumentNullException(nameof(specialTest));
        TemplateType = templateType ?? throw new ArgumentNullException(nameof(templateType));
        StandardTime = standardTime;
        StartDate = startDate;
    }

    public int Id { get; set; }
    public string Title { get; set; }
    public bool SignaturePerLine { get; set; }
    public string SpecialTest { get; set; }
    public TemplateType TemplateType { get; set; }
    public int StandardTime { get; set; }
    public DateTime StartDate { get; set; }
}

public class TemplateType
{
    public TemplateType(int id, string name, string description, bool pictures, bool video, bool matrix)
    {
        Id = id;
        Name = name;
        Description = description;
        Pictures = pictures;
        Video = video;
        Matrix = matrix;
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public bool Pictures { get; set; }
    public bool Video { get; set; }
    public bool Matrix { get; set; }
}

public class Mapper
{
    public TemplateDb ToDb(Template template)
    {
        return new TemplateDb(
            id: template.Id,
            title: template.Title,
            signaturePerLine: template.SignaturePerLine,
            specialTest: template.SpecialTest,
            templateTypeId: template.TemplateType.Id,
            standardTime: template.StandardTime,
            startDate: template.StartDate
            );
    }

    public Template ToDomain(TemplateDb templateDb, TemplateTypeDb templateTypeDb)
    {
        return new Template(
            id: templateDb.Id,
            title: templateDb.Title,
            signaturePerLine: templateDb.SignaturePerLine,
            specialTest: templateDb.SpecialTest,
            templateType: new TemplateType(
                id: templateTypeDb.Id,
                name: templateTypeDb.Name,
                description: templateTypeDb.Description,
                pictures: templateTypeDb.Pictures,
                video: templateTypeDb.Video,
                matrix: templateTypeDb.Matrix
            ),
            standardTime: templateDb.StandardTime,
            startDate: templateDb.StartDate);
    }
}

CodePudding user response：

Something like:

string sql = $@"insert into dbo.Template (Title, SignaturePerLine, StandardTime, StartDate, SpecialTest, TemplateTypeId) 
      values (@Template.Title, @Template.SignaturePerLine, @Template.StandardTime, @Template.StartDate, @Template.SpecialTest, @TemplateType.Id);";


connection.Execute(sql,new {Template = template, TemplateType = templateType});