Try .NET /elinq/
Powered by Try .NET

SQL Server UPDATE JOIN

EF does a thorough work to track entity state. In cases where the fact of change is not clear, it's usually better to let EF to manage the update.

ELINQ (pure SQL) is preferred when we don't want to retrieve the entity or a bulk update is needed.

A) SQL Server UPDATE INNER JOIN example

var rows = DbContext.Database.Query((Commissions commissions, Commissions c, Targets t) =>
{
    UPDATE(commissions)
        .SET(() => commissions.Commission = c.BaseAmount * t.Percentage);
    FROM(c).JOIN(t).ON(c.Target == t);
});

Console.WriteLine($"{rows} rows affected");

B) SQL Server UPDATE LEFT JOIN example

Declarations:

readonly Commissions c1 = new Commissions()
{
    StaffId = 6,
    BaseAmount = 100000M,
};

readonly Commissions c2 = new Commissions()
{
    StaffId = 7,
    BaseAmount = 120000M,
};

We INSERT, UPDATE and SELECT in a nice single compound statement (Run to see):

var coalesce = 0.1M;
var query = DbContext.Commissions.Query((Commissions commissions, Commissions c, Targets t) =>
{
    var set = commissions.@using((commissions.StaffId, commissions.BaseAmount, commissions.TargetId));
    INSERT().INTO(set);
    VALUES(set.RowFrom(c1), set.RowFrom(c2));

    Semicolon();

    UPDATE(commissions)
        .SET(() => commissions.Commission = c.BaseAmount * COALESCE(t.Percentage, coalesce));
    FROM(c).LEFT_JOIN(t).ON(c.Target == t);

    Semicolon();

    return SelectAll(commissions);
});

foreach (var commission in query)
    Console.WriteLine((commission.StaffId, commission.TargetId?.ToString() ?? "NULL", commission.BaseAmount, commission.Commission));

< BACK | HOME