Left Join using LAMBDA to get Result in API

Question

How to implement this Join which is in the code below into C# using LAMBDA

Select 
        VD.Id
        , VD.BusinessAddress
        , VD.BusinessDesc
        , VD.BusinessEmail
        , VD.BusinessName
        , VD.BusinessZip
        , VD.ContactPerson
        , VD.ContactNo
        , VD.ProfileUrl
        , L.Name
        , BC.BusinessCategory

        from vendorDomain VD WITH(NOLOCK)
        left Join Location L WITH(NOLOCK) ON VD.City = L.Id
        left join Business_Category BC WITH(NOLOCK) ON VD.BusinessCategory = BC.BusinessId

where VD.IsDeleted = 0

I have to implement the join operation in the following API:

[HttpGet]
    public async Task<IActionResult> Get()
    {
        var VendorList =await _vendorRepository.Query().Where(x => x.IsDeleted == false).ToListAsync();

        return Ok(VendorList);

    }

There are alot of examples out there but are way to confusing for a novice developer..

EDIT:

This is what I have tried as of now:

var employees = from vndr in context.vendorDomain
                        join C in context.Location on vndr.City equals C.Id into dep
                        from dept in dep.DefaultIfEmpty()

                        select new

                        {
                            vndr.BusinessAddress,
                            vndr.BusinessDesc,
                            vndr.BusinessEmail,
                            vndr.BusinessName,
                            vndr.BusinessWebsite,
                            vndr.BusinessZip,
                            vndr.ContactNo,
                            vndr.ContactPerson,
                            vndr.Created_At,
                            vndr.ProfileUrl,
                            vndr.Url,
                            dept.Name
                        };

Do you just want to translate the sql query to linq or do you want to somehow merge it with the `VendorList` variable? — Marco, Feb 28 '19 at 08:15
@Marco I need to add The City name and the Business Category to the VendorList Table — that_guy_jrb, Feb 28 '19 at 08:19
Again: Do you just need a translation of the SQL query to a LINQ query or do you need more - i.e. somehow work with the VendorList variable? — Marco, Feb 28 '19 at 08:22

Marco · Accepted Answer · 2019-02-28T11:49:06.620

We will do things first: do the joins and create a view model class that you will return. Because returning anonymous object and using dynamic does get messy.

ViewModel for the joined entities:

public class EmployeesViewModel
{
    public string BusinessAddress { get; set; } 
    public string BusinessDesc { get; set; } 
    public string BusinessEmail { get; set; } 
    /* ....all remaining properties */
}

Then we join them properly and select them as an EmployeeViewModel:

var employees = from vndr in context.vendorDomain
                join loc in context.Location on vndr.City equals loc.Id
                join bus in context.Business_Category on vndr.BusinessCategory = bus.BusinessId
                select new EmployeeViewModel
                {
                    BusinessAddress = vndr.BusinessAddress,
                    BusinessDesc = vndr.BusinessDesc,
                    BusinessEmail = vndr.BusinessEmail,
                    /* ... remaining properties here*/
               };

Or, if you want the method syntax:

var employees = context.vendorDomain
                .Join(context.Location,
                       vndr => vndr.City,
                       loc => loc.Id,
                       (vndr, loc) => new { vndr, loc,})
                .Join(context.Business_Category,
                       vndr_loc.vndr.BusinessCategory,
                       bus.BusinessId,
                       (vndr_loc, bus) => new {vndr_loc.vndr, vndr_loc.loc, bus})
                .Select(x => new EmployeeViewModel{
                    BusinessAddress = vndr.BusinessAddress,
                    BusinessDesc = vndr.BusinessDesc,
                    BusinessEmail = vndr.BusinessEmail,
                    /* ... remaining properties here*/
                });

As per your comment, you need to print the vendorList after the join. Now that is pretty vague, but I assume you want to submit both to your client / view, so again, we create a ViewModel class for it:

public class EmployeeVendorListViewModel
{
   public VendorList VendorList { get; set; }
   public EmployeeViewModel Employees { get; set; }
}

The last thing we do is glue it all together in your ActionMethod and return it:

[HttpGet]
public async Task<IActionResult> Get()
{
    //renamed using a lower case "v"
    var vendorList = await _vendorRepository.Query()
                         .Where(x => x.IsDeleted == false)
                         .ToListAsync();

    //the join from earlier. You should put it in a repo somewhere, so it does not clutter your controller
    var employees = from vndr in context.vendorDomain
                    join loc in context.Location on vndr.City equals loc.Id
                    join bus in context.Business_Category on vndr.BusinessCategory = bus.BusinessId
                    select new EmployeeViewModel
                    {
                        BusinessAddress = vndr.BusinessAddress,
                        BusinessDesc = vndr.BusinessDesc,
                        BusinessEmail = vndr.BusinessEmail,
                        /* ... remaining properties here*/
                    };
    //create the final view model and return it
    var vm = new EmployeeVendorListViewModel 
    {
        VendorList = vendorList,
        Employees = employees
    }

    return Ok(vm);
}

If you want to use NOLOCK in your query, you have to wrap it in a TransactionScope. This has already been answered here on StackOverflow: NOLOCK with Linq to SQL

Thanks Marco. Also wanted to ask if all this can be done using Lambda expression? — that_guy_jrb, Feb 28 '19 at 08:56
I've updated my answer. I'd personally go for the query syntax, though. It is much more readable and concise. — Marco, Feb 28 '19 at 11:49
I need a little assistance with one more code, if you can help me with the same? — that_guy_jrb, Feb 28 '19 at 12:02
Just ask a new question on SO. That's what this is all about ;) — Marco, Feb 28 '19 at 12:02
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/189199/discussion-between-that-guy-jrb-and-marco). — that_guy_jrb, Feb 28 '19 at 12:16

Left Join using LAMBDA to get Result in API

1 Answers1