LinqToSQL Left outer join

Question

I want to replicate this sql query but i'm having dificulty trying to find the solution;

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

This is where I've got up to but the error I get is "Object reference no set to in instance of an object".

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();

Sorry I'm very much a newbie. ClientProperties is defined as this as its used to collate data from a collation of csv files.

    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;

    }

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you? — NetMage, Jun 25 '19 at 17:56

KakashiJack · Accepted Answer · 2019-06-25T18:57:24.767

Something like this?:

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();

I supose your "ClientProperties()" object is a context or something like that. In this case you need do something like:

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();

}

And then you can access to the object easily:

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

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

Sorry I'm very much a newbie, I've added more details on what ClientProperties is. — Martin, Jun 26 '19 at 07:44

score 0 · Answer 2 · answered Jun 26 '19 at 08:07

Hi Thanks for all you advice as it helped me worked it out this was my end result;

            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();

LinqToSQL Left outer join

2 Answers2