CODEWITHSUNDEEP
ormlite-servicestack
RichHosek
RichHosek

Reputation: 41

Can I do an UPDATE on a JOIN query with OrmLite on ServiceStack?

I want to do an update for a specific field on a table based on the results from a query that includes a join. Using OrmLite with ServiceStack.

My Classes are as follows: 

public class Document
{
    public int Id { get; set; }
    public string BCL_Code { get; set; }
    public bool IsActive { get; set; }
    public int DocumentTypeId { get; set; }
}

public class DocumentType
{
    public int Id { get; set; }
    public string TypeName { get; set; }
}

Trying to do the Update on the Join with the following code:

var q = db.From<Document>()
    .Join<Document, DocumentType>(
        (doc, type) => doc.DocumentTypeId == type.Id)
    .Where(d => d.BCL_Code == "FR49")
    .And<DocumentType>(t => t.TypeName == "Enrollment Process");

db.UpdateOnly(new Document { IsActive = false }, onlyFields: q);

I know I can update specific fields, and I know how to do joins, but when I try to include a join in the query, and then do an UpdateOnly, I get an error message on the db.UpdateOnly line:

The multi-part identifier "DocumentType.TypeName" could not be bound.

Is it possible to do an Update on a Join Query? If so, what is the proper way to do it?

Upvotes: 3

Views: 407

Answers (1)

mythz
mythz

Reputation: 143284

There's no Typed APIs for Update From Table in OrmLite yet, you can add a feature request for it.

In the meantime you can use Custom SQL, e.g:

db.ExecuteSql(@"UPDATE Document SET IsActive = @isActive
  FROM Document d 
       INNER JOIN DocumentType t ON (d.DocumentTypeId = t.Id)
  WHERE d.BCL_Code = 'FR49'
    AND t.TypeName = 'Enrollment Process'", 
  new { isActive = false });

Upvotes: 1

Related Questions