如何在LINQ中的Where Exists中使用Except

我有以下SQL Server查询，我试图重写为LINQ：

-- find people not already in People table based on name match but 
-- other non-id elements not matching. Person_id will not match 
-- between tables. Need person_id at the end of it all.
select p1.person_id, p1.name, p1.birthdate, p1.address
from Persons1 p1
join Persons2 p2
  on p1.name = p2.name
where exists
(
  select p1.name, p1.birthdate, p1.address
  except
  select p2.name, p2.birthdate, p2.address
);

字符串
我想使用Except集合运算符，因为它可以轻松地帮助我识别不匹配的数据。然而，我在如何将其转换为C# LINQ方法语法方面有点欠缺。
下面是一个例子：

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public DateTime Birthdate { get; set; }
}

var sourcePeople = new List<Person>
{
    new Person { Id = 1, Name = "John Doe", Address = "Test Address1", Birthdate = DateTime.Today },
    new Person { Id = 2, Name = "Jane Doe", Address = "Test Address2", Birthdate = DateTime.Today },
    new Person { Id = 3, Name = "Doug Fir", Address = "Test Address3", Birthdate = DateTime.Today }
};

var destinationPeople = new List<Person>
{
    new Person { Id = 4, Name = "John Doe", Address = "Test Address1", Birthdate = DateTime.Today },
    new Person { Id = 5, Name = "Jane Doe", Address = "Test Address5", Birthdate = DateTime.Today },
    new Person { Id = 6, Name = "Doug Fir", Address = "Test Address3", Birthdate = DateTime.Today }
};

型
这给了我差异，但没有他们的id。我不能包含id，因为这样会返回每一行，因为id不匹配

var result = sourcePeople
    .Select(i => new
    {
        i.Name,
        i.Birthdate,
        i.Address
    })
    .Except(destinationPeople
        .Select(p => new
        {
            p.Name,
            p.Birthdate,
            p.Address
        }))
    .ToList();

型
如何返回不在目标用户中的所有sourcePeople？但仅针对像我上面使用的属性子集中的差异。因此，我希望只看到ID 3。

var result2 = sourcePeople
    .Where(s => destinationPeople.???)
    .Select(s => s.Id);

型
是的，我知道Any()，但我不知道它在这种情况下会如何写。

Except不适用于您的示例，因为您只比较对象，而不是集合。
下面是一个实现SQL语句的示例：

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public DateTime Birthdate { get; set; }
}

private static List<Person> TestLinq()
{
    var sourcePeople = new List<Person>
    {
        new Person { Id = 1, Name = "John Doe", Address = "Test Address1", Birthdate = DateTime.Today },
        new Person { Id = 2, Name = "Jane Doe", Address = "Test Address2", Birthdate = DateTime.Today },
        new Person { Id = 3, Name = "Doug Fir", Address = "Test Address3", Birthdate = DateTime.Today }
    };

    var destinationPeople = new List<Person>
    {
        new Person { Id = 4, Name = "John Doe", Address = "Test Address1", Birthdate = DateTime.Today },
        new Person { Id = 5, Name = "Jane Doe", Address = "Test Address5", Birthdate = DateTime.Today },
        new Person { Id = 6, Name = "Doug Fir", Address = "Test Address3", Birthdate = DateTime.Today }
    };

    var desiredSourcePeople = sourcePeople
        .Join(destinationPeople,
            sourcePerson => sourcePerson.Name,
            destinationPerson => destinationPerson.Name,
            (sourcePerson, destinationPerson) => new
            {
                SpId = sourcePerson.Id,
                SpName = sourcePerson.Name,
                SpBirthdate = sourcePerson.Birthdate,
                SpAddress = sourcePerson.Address,
                DpName = destinationPerson.Name,
                DpBirthdate = destinationPerson.Birthdate,
                DpAddress = destinationPerson.Address
            }
        )
        .Where(joinedPerson =>
            joinedPerson.SpBirthdate != joinedPerson.DpBirthdate
            || joinedPerson.SpAddress != joinedPerson.DpAddress)
        .Select(joinedPerson => new Person
        {
            Id = joinedPerson.SpId,
            Name = joinedPerson.SpName,
            Birthdate = joinedPerson.SpBirthdate,
            Address = joinedPerson.SpAddress
        })
        .ToList();

    return desiredSourcePeople;
}

字符串
如果你想避免复杂的Where子句，你可以写一个方法来进行比较：

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public DateTime Birthdate { get; set; }

    public bool IsSameExceptId(Person person)
    {
        return Name==person.Name && Address == person.Address && Birthdate == person.Birthdate;
    }
}

private static List<Person> TestLinq()
{
    var sourcePeople = new List<Person>
    {
        new Person { Id = 1, Name = "John Doe", Address = "Test Address1", Birthdate = DateTime.Today },
        new Person { Id = 2, Name = "Jane Doe", Address = "Test Address2", Birthdate = DateTime.Today },
        new Person { Id = 3, Name = "Doug Fir", Address = "Test Address3", Birthdate = DateTime.Today }
    };

    var destinationPeople = new List<Person>
    {
        new Person { Id = 4, Name = "John Doe", Address = "Test Address1", Birthdate = DateTime.Today },
        new Person { Id = 5, Name = "Jane Doe", Address = "Test Address5", Birthdate = DateTime.Today },
        new Person { Id = 6, Name = "Doug Fir", Address = "Test Address3", Birthdate = DateTime.Today }
    };

    var desiredSourcePeople = sourcePeople
        .Join(destinationPeople,
            sourcePerson => sourcePerson.Name,
            destinationPerson => destinationPerson.Name,
            (sourcePerson, destinationPerson) => new
            {
                SourcePerson = sourcePerson,
                DestinationPerson = destinationPerson
            }
        )
        .Where(joinedPerson => !joinedPerson.SourcePerson.IsSameExceptId(joinedPerson.DestinationPerson))
        .Select(joinedPerson => joinedPerson.SourcePerson)
        .ToList();

    return desiredSourcePeople;
}

型
还要注意的是，第二个例子将对象存储在joinedPerson中，而不是拆分为所需的值。

如何在LINQ中的Where Exists中使用Except

1条答案

相关问题

热门标签

最新问答