CODEWITHSUNDEEP
.netdapper
Greg McGuffey
Greg McGuffey

Reputation: 3316

How to do insert with a FK that is an object in Dapper?

I have an object, Ticket, that has a property of type User. In the database, the Ticket table has a FK to the User table. Is there an easy way to pass the ID of the assigned user on the ticket into a query?

public class Ticket
{
    public int Id{get;}
    public User AssignedTo{get;set;}
}
public class User
{
    public int Id{get;}
}
public Ticket Save(Ticket newTicket)
{
    var sql = 
       @"
        Declare @localId int
        Insert into Ticket (AssignedTo)
        Values (@AssignedTo)

        Select @localId = CAST(SCOPE_IDENTITY() as int)

        Select
             Id
            ,AssignedTo
        From Ticket
        Where Id = @localId";
    var this.connection.Query<Ticket>(sql, newTicket);           
}

The above doesn't work because newTicket.AssignedTo is a User object, but what I need is to store the ID of the user. Is there a supported way to do this with Dapper?

Upvotes: 1

Views: 942

Answers (1)

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

I would recommend to divide this functionality into two function. One is inserting data into Ticket table and return the last identity and second function is retrieve the record using that id.

Note: In your existing function you have passed parameter in wrong manner. You can do something like this with your existing function - 

public Ticket Save(Ticket newTicket)
{
    string sql = string.Empty;
    sql = 
       @"
        Insert into Ticket (AssignedTo)
        Values (@AssignedTo);

        SELECT CAST(SCOPE_IDENTITY() as int);";

    var id = this.connection.Query<int>(sql, new { AssignedTo = newTicket.AssignedTo}).Single(); 

    sql = @"Select Id, AssignedTo
                From Ticket
                Where Id = @localId";
     return this.connection.Query<Ticket>(sql, new { localId = id }).Single();
}

But there is no need to execute select query again if i assume you want only 2 fields Id and AssignedTo. So in that case what you can do is assign newly generated id to your existing object newTicket, like this - 

public Ticket Save(Ticket newTicket)
{
    string sql = string.Empty();
    sql = 
       @"
        Insert into Ticket (AssignedTo)
        Values (@AssignedTo);

        SELECT CAST(SCOPE_IDENTITY() as int);";

    var id = this.connection.Query<int>(sql, new { AssignedTo = newTicket.AssignedTo}).Single(); 

    // Assign the last identity value to the ID proerpty of newTicket object
    newTicket.Id = id;

    // Return the existing object
    return newTicket
}

good luck...

Upvotes: 1

Related Questions