[Fork] [Fork] SO Question Group By With Having Clause LINQ 27730743

CSharp · Console

/Program.cs

using System;
using System.Collections.Generic;
using System.Linq;

public class Program
{
	public static void Main()
	{
		List<Contract> Contracts = new List<Contract>()
		{
		new Contract
		{
		ID = 1, LearningPackageID = 2
		}

		, new Contract
		{
		ID = 2, LearningPackageID = 4
		}

		, new Contract
		{
		ID = 3, LearningPackageID = 6
		}

		, new Contract
		{
		ID = 4, LearningPackageID = 8
		}
		}

		;
		List<DepositHistory> DepositHistories = new List<DepositHistory>()
		{
		new DepositHistory
		{
		CotractID = 1, Value = 100
		}

		, new DepositHistory
		{
		CotractID = 2, Value = 50
		}

		, new DepositHistory
		{
		CotractID = 3, Value = 200
		}
		}

		;
		List<LearningPackage> LearningPackages = new List<LearningPackage>()
		{
		new LearningPackage
		{
		ID = 2, COntractValue = 100
		}

		, new LearningPackage
		{
		ID = 4, COntractValue = 200
		}

		, new LearningPackage
		{
		ID = 6, COntractValue = 300
		}

		, new LearningPackage
		{
		ID = 8, COntractValue = 400
		}
		}

		;
		var result =
			from a in Contracts
			join b in DepositHistories on a.ID equals b.CotractID into e
			from f in e.DefaultIfEmpty()join c in LearningPackages on a.LearningPackageID equals c.ID
			group f by new
			{
			a.ID, c.COntractValue
			}

				into g
				where g.Sum(x => x == null ? 0 : x.Value) < g.Key.COntractValue || g.Sum(x => x == null ? 0 : x.Value) == 0
				select new
				{
				ID = g.Key.ID, Value = g.Sum(x => x == null ? 0 : x.Value), ContractValue = g.Key.COntractValue
				}

		;
		;
		foreach (var item in result)
		{
			Console.WriteLine(item);
		}
	}
}

public class Contract
{
	public int ID
	{
		get;
		set;
	}

	public int LearningPackageID
	{
		get;
		set;
	}
}

public class DepositHistory
{
	public int CotractID
	{
		get;
		set;
	}

	public int Value
	{
		get;
		set;
	}
}

public class LearningPackage
{
	public int ID
	{
		get;
		set;
	}

	public int COntractValue
	{
		get;
		set;
	}
}