How to perform a join on two parameters in LINQ

Question

I have the following code:

var allCountryRates = (from c in allCountryCombinations
                        join r in Db.PaymentRates_VisaImmigrationPermit 
                            on new { c.HomeCountryId, c.HostCountryId } 
                            equals new { r.HomeCountryId, r.HostCountryId }
                        select r);

Basically, if an r is found in c, based on both conditions of the join, I want to select r. If no r is found for c, then I want to generate an empty record with a Guid.NewGuid() and select that.

Is this possible? I feel like I am pretty close, but not sure how to take it further.

My code above gives me an error on the join which reads, "The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'."

Ref. How to do joins in LINQ on multiple fields in single join

Edit: Latest version.

var allCountryRates = (from c in allCountryCombinations
                        join r in Db.PaymentRates_VisaImmigrationPermit
                            on new { home = (Guid?)c.HomeCountryId, host = (Guid?)c.HostCountryId }
                            equals new { home = r.HomeCountryId, host = r.HostCountryId }
                        into matches
                        from rate in matches.DefaultIfEmpty(new PaymentRates_VisaImmigrationPermit
                        {
                            Id = Guid.NewGuid(),
                            HomeCountryId = c.HomeCountryId,
                            HostCountryId = c.HostCountryId
                        })
                        select new VisaAndImmigrationPermitRate
                        {
                            Id = rate.Id,
                            HomeCountryId = (Guid)rate.HomeCountryId,
                            HomeCountry = c.HomeCountry,
                            HostCountryId = (Guid)rate.HostCountryId,
                            HostCountry = c.HostCountry
                        });

Answer 1

You can try something like this: var allCountryRates = (from r in Db.PaymentRates_VisaImmigrationPermit where allCountryCombinations.Any(c => c.HomeCountryId == r.HomeCountryId && c.HostCountryId == r.HostCountryId) select r).FirstOrDefault();

I created a ConsoleApp to test your question and it worked.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace StackOverFlowConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {

            List<PaymentRates_VisaImmigrationPermit> PaymentRates_VisaImmigrationPermits = new List<PaymentRates_VisaImmigrationPermit>() { 
            new PaymentRates_VisaImmigrationPermit(){HomeCountryId= new Guid("5fb7097c-335c-4d07-b4fd-000004e2d28a"),HostCountryId=new Guid("5fb7097c-335c-4d07-b4fd-000004e2d28a")},
            new PaymentRates_VisaImmigrationPermit(){HomeCountryId=new Guid("5fb7097c-335c-4d07-b4fd-000004e2d28b"),HostCountryId=new Guid("5fb7097c-335c-4d07-b4fd-000004e2d28b")}
            };
            List<allCountryCombination> allCountryCombinations = new List<allCountryCombination>() {             
            new allCountryCombination(){HomeCountryId=new Guid("5fb7097c-335c-4d07-b4fd-000004e2d28b"),HostCountryId=new Guid("5fb7097c-335c-4d07-b4fd-000004e2d28b")},
            new allCountryCombination(){HomeCountryId=new Guid("5fb7097c-335c-4d07-b4fd-000004e2d28c"),HostCountryId=new Guid("5fb7097c-335c-4d07-b4fd-000004e2d28c")}
            };

            var allCountryRates = (from r in PaymentRates_VisaImmigrationPermits where allCountryCombinations.Any(c => c.HomeCountryId == r.HomeCountryId && c.HostCountryId == r.HostCountryId) select r).FirstOrDefault();

            int sa = 0;
        }

        class PaymentRates_VisaImmigrationPermit
        {
            public Guid? HomeCountryId { get; set; }

            public Guid? HostCountryId { get; set; }
        }

        class allCountryCombination
        {
            public Guid HomeCountryId { get; set; }

            public Guid HostCountryId { get; set; }
        }

    }
}

Answer 2

Since you don't want an inner join, but rather a left join, you'll want to use the GroupJoin operator instead of the Join operator.

var allCountryRates = (from c in allCountryCombinations
                        join r in Db.PaymentRates_VisaImmigrationPermit 
                            on new { c.HomeCountryId, c.HostCountryId } 
                            equals new { r.HomeCountryId, r.HostCountryId }
                        into matches
                        let match = matches.Any() ? matches.First() : emptyMatch
                        select match);