SQL для LINQ помогает вычитать данные из одной таблицы из другой таблицы (несколько строк)

Предполагая, что в вашем приложении определен некоторый контекст БД, вы можете использовать:

context.GoalHours
    .Join(
        context.AccumulatedHours, 
        x => x.ID, 
        x => x.GH_ID, 
        (x, y) => new { x.ID, x.Target, y.Completed, Remaining = x.Target - y.Completed })
    .ToArray(); // or .ToArrayAsync()

Ниже я определяю два класса:

    public class AccumulatedHours
    {
        public int Id { get; set; }
        public int UserId { get; set; }
        public int Completed { get; set; }
    }

    public class GlobalHours
    {
        public int Id { get; set; }
        public int UserId { get; set; }
        public int Target { get; set; }
    }

Затем я использовал ниже Linq. Вам нужно использовать on GH.UserId equals AH.UserId into userInfos select new для присоединения:

var ah = new List<AccumulatedHours>
{
    new AccumulatedHours {Id = 1, UserId = 1, Completed = 5},
    new AccumulatedHours {Id = 2, UserId = 1, Completed = 8}
};
var gh = new List<GlobalHours>
{
    new GlobalHours {Id = 1, UserId = 1, Target = 20}
};

var joined = (from GH in gh
    join AH in ah on GH.UserId equals AH.UserId into userInfos
    select new
    {
        userId = GH.UserId,
        target = GH.Target,
        sumOfCompleted = userInfos.Sum(r => r.Completed),
        remaining = GH.Target - userInfos.Sum(r => r.Completed)
    }).ToList();

Результаты такие:

Во-первых, вам не следует обращаться к данным таблицы базы данных и выполнять .ToList() перед применением всех соединений, фильтров и селекторов. Это приведет к излишней загрузке данных (возможно, всех строк и всех столбцов) в коллекцию в памяти. После этого любые запросы, примененные к этим спискам, не будут иметь доступа к исходным ключам и индексам и будут выполнять в памяти эквивалент сканирования таблицы.

Судя по вашему утверждению «Может быть несколько журналов» и столбцам идентификаторов, указанным в вашем объединении, становится очевидным, что связь между GoalHours и AccumulatedHours — «один ко многим». Чтобы получить желаемые результаты, ваш запрос необходимо сгруппировать по GoalHours.ID и GoalHours.Target, а затем суммировать значения AccumulatedHours.Completed.

Если существует вероятность наличия GoalHours без совпадающих AccumulatedHours строк (что может быть обычным явлением, если часы еще не записаны), вам потребуется выполнить LEFT JOIN между этими таблицами или эквивалент LINQ. Вам также потребуется обработать условие нулевой суммы с помощью функции SQL ISNULL() или COALESCE() или эквивалентного оператора объединения нулей C# ??.

Предполагая, что необходимо левое соединение, я считаю, что вы можете достичь желаемых результатов с помощью чего-то вроде следующего SQL:

DECLARE @UserID INT = 4

SELECT
    gh.ID,
    gh.Target AS GoalHours,
    ISNULL(SUM(ah.Completed), 0) AS Completed,
    gh.Target - ISNULL(SUM(ah.Completed), 0) AS Remaining
FROM GoalHours gh
LEFT JOIN AccumulatedHours ah
    ON gh.ID = ah.GH_ID
WHERE gh.UserID = @UserID
GROUP BY gh.ID, gh.Target;

Левые соединения сложны в LINQ. Один из подходов — выполнить «групповое объединение» с использованием синтаксиса join ... on ... into ..., который объединяет строки с левой стороны объединения с (возможно, пустой) коллекцией строк с правой стороны объединения. Это удобно обеспечивает как левое соединение, так и эквивалент группировки. Затем необходимо выбрать, суммировать и объединить результаты.

Тогда эквивалент запроса LINQ будет выглядеть примерно так:

int userID = 4

var query =
    from gh in dbContext.GoalHours
    where gh.UserID == userID
    join ah in dbContext.AccumulatedHours
        on gh.ID equals ah.GH_ID
        into ahGroup
    select new
    {
        ID = gh.ID,
        GoalHours = gh.Target,
        Completed = ahGroup.Sum(ah => (decimal?)ah.Completed) ?? 0,
        Remaining = gh.Target - (ahGroup.Sum(ah => (decimal?)ah.Completed) ?? 0)
    };

Как указано в комментариях ниже, по крайней мере некоторые версии платформы не поддерживают подход GroupJpoin. Ниже приведено альтернативное LINQ-представление левого соединения, использующее технику .DefaultIfEmpty(). (Однако логика обработки null может нуждаться в некоторых изменениях.)

var query2 = 
    from gh in dbContext.GoalHours
    where gh.UserID == userID
    join ah in dbContext.AccumulatedHours
        on gh.ID equals ah.GH_ID
        into ahGroup
    from ahd in ahGroup.DefaultIfEmpty()
    group  new { gh, ahd } by new { gh.ID, gh.Target } into grouped
    select new
    {
        ID = grouped.Key.ID,
        GoalHours = grouped.Key.Target,
        Completed = grouped.Sum(g => (decimal?)g.ahd?.Completed) ?? 0,
        Remaining = grouped.Key.Target - (grouped.Sum(g => (decimal?)g.ahd?.Completed) ?? 0)
    };

Оператор приведения (decimal?) необходим для правильной обработки сумм для пустых списков. Функция SQL-сервера SUM() вернет значение null для пустого списка (или списка, не содержащего ненулевых значений). Использование этого приведения вместе с ?? 0 гарантирует, что сумма для пустого списка вернет ноль. Вам нужен соответствующий тип C#, соответствующий типу вашего столбца Completed.

(Вышеупомянутое не проверено. Если я допустил ошибку, я приветствую комментарии или исправления.)