Метод расширения, который работает как левое соединение с синтаксисом Join
public static class LinQExtensions
{
public static IEnumerable<TResult> LeftJoin<TOuter, TInner, TKey, TResult>(
this IEnumerable<TOuter> outer, IEnumerable<TInner> inner,
Func<TOuter, TKey> outerKeySelector,
Func<TInner, TKey> innerKeySelector,
Func<TOuter, TInner, TResult> resultSelector)
{
return outer.GroupJoin(
inner,
outerKeySelector,
innerKeySelector,
(outerElement, innerElements) => resultSelector(outerElement, innerElements.FirstOrDefault()));
}
}
, просто написал его в ядре .NET и, похоже, работает как ожидалось.
Малый тест:
var Ids = new List<int> { 1, 2, 3, 4};
var items = new List<Tuple<int, string>>
{
new Tuple<int, string>(1,"a"),
new Tuple<int, string>(2,"b"),
new Tuple<int, string>(4,"d"),
new Tuple<int, string>(5,"e"),
};
var result = Ids.LeftJoin(
items,
id => id,
item => item.Item1,
(id, item) => item ?? new Tuple<int, string>(id, "not found"));
result.ToList()
Count = 4
[0]: {(1, a)}
[1]: {(2, b)}
[2]: {(3, not found)}
[3]: {(4, d)}
name
INSERT INTO target (column list)
SELECT column list
FROM source
LEFT JOIN target
ON target.name = source.name
WHERE target.name IS NULL
или
INSERT INTO target (<column list>)
SELECT <column list> FROM source
WHERE NOT EXISTS (
SELECT * from target
WHERE target.name = source.name
)