LinqToSQL左外部联接

Question

我想复制这个sql查询，但我很难找到解决方案；

SELECT C.Propref
FROM [dbo].[ClientProperties] C
LEFT OUTER JOIN  [dbo].[Properties] P ON C.[PROPREF] = P.[PROPREF] AND P.Contract = 'TXT'
WHERE P.[PROPREF] IS null

这就是我所得到的，但我得到的错误是“对象引用没有设置为一个对象的实例”。

var query = (from c in ClientProperties()
                    join p in db.Properties.Where(wc => wc.Contract == _contractId) on c.Place_reference equals p.Theirref into cp
                    from found in cp.DefaultIfEmpty()
                    select new
                    {
                        UPRN = c.Place_reference,
                    }).ToList();

抱歉，我是个新手。ClientProperties的定义是这样的，因为它用于从csv文件的排序中整理数据。

    private IEnumerable<ClientProperty> ClientProperties()
    {
        CsvContext cc = new CsvContext();

        if (Directory.Exists(_interfaceInProperty))
        {
            IEnumerable<ClientProperty> properties = new List<ClientProperty>();
            var files = Directory.GetFiles(_interfaceInProperty, "Prop*.csv");

            foreach (var f in files)
            {
                properties = cc.Read<ClientProperty>(f, inputFileDescription);
            }
            return properties;
        }

        return null;

    }

Answer 1

像这样吗？：

var query = db.ClientProperties.GroupJoin(
    db.Properties,
    a => a.PROPREF,
    b => b.PROPREF,
    (a, b) => new { ClientProperties = a, Properties = b })
    .SelectMany(x => x.ClientProperties.Where(y => y.Contract == "TXT" && string.IsNullOrEmpty(y.PROPREF.ToString())),
    (a, b) => new { ClientProperties = a, Properties = b }).ToList();

我假设你的"ClientProperties()“对象是一个上下文或者类似的东西。在这种情况下，您需要执行以下操作：

using (var db = new ClientProperties())
{

    var query = db.ClientProperties.GroupJoin(
        db.Properties,
        a => a.PROPREF,
        b => b.PROPREF,
        (a, b) => new { ClientProperties = a, Properties = b })
        .SelectMany(x => x.ClientProperties.Where(y => y.Contract == "TXT" && string.IsNullOrEmpty(y.PROPREF.ToString())),
        (a, b) => new { ClientProperties = a, Properties = b }).ToList();

}

然后您就可以轻松地访问对象了：

var response = query.FirstOrDefault().ClientProperties.Propref;

foreach (var item in query)
{
    var each_response = item.ClientProperties.Propref;
}

Answer 2

嗨，谢谢你的建议，因为它帮助我解决了这个问题，这就是我的最终结果；

            var query = (from c in ClientProperties()
                join p in db.Properties.Where(wc => wc.Contract == _contractId) on c.PROPREF equals p.PROPREF into cp
                from found in cp.DefaultIfEmpty()
                where found == null
                select new
                {
                    UPRN = c.PROPREF,
                    Address = c.Location_address_1
                }).ToList();