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

score 2 · Accepted Answer · answered Mar 31 '14 at 17:41

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

answered Mar 31 '14 at 17:41

Servy

202,030
26
332
449

Thanks but when I try this it says, "The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'GroupJoin'. " – user1477388 Mar 31 '14 at 17:50
1

@user1477388 The types don't match. One of these properties has a different type than the property of another name from the other table. They need to be the same type. – Servy Mar 31 '14 at 17:51
`r` has nullable Guids and `c` does not. I am trying to cast them but it isn't working... I also tried `equals new { r.HomeCountryId.Value, r.HostCountryId.Value }` but it says, "An anonymous type cannot have multiple properties with the same name." – user1477388 Mar 31 '14 at 17:53
@user1477388 "Isn't working" is entirely unhelpful. What happens when you cast one to the other, specifically? – Servy Mar 31 '14 at 17:54
I was editing my comment, does the information I added help you help me? – user1477388 Mar 31 '14 at 17:55
1

@user1477388 First off, that will break if there are any null values, second, you now have two properties named `Value` rather than a property named `HomeCountryId` and one called `HostCountryId`. Explicitly specify the names so that you don't have two properties with the same name. – Servy Mar 31 '14 at 17:56
I now have `on new { home = c.HomeCountryId, host = c.HostCountryId } equals new { home = r.HomeCountryId.Value, host = r.HostCountryId.Value }` which seems to work but, as you say, it will break if there are any nulls. Do you have a better idea? – user1477388 Mar 31 '14 at 17:58
1

@user1477388 You have two types. One has all of the values of the other, but with one extra one. Instead of converting the broader type to the narrower type, convert the narrower type to the broader type. – Servy Mar 31 '14 at 17:59
Good thinking, I think this is what you are saying: `on new { home = (Guid?)c.HomeCountryId, host = (Guid?)c.HostCountryId } equals new { home = r.HomeCountryId, host = r.HostCountryId }` – user1477388 Mar 31 '14 at 18:01
`match` becomes a type of `PaymentRates_VisaImmigrationPermit` or `r`. I would like to also get the values of `c` in `match`. Can I do that? I updated my question with the latest code. – user1477388 Mar 31 '14 at 18:16
@user1477388 You can, but you don't have to, and probably shouldn't try to, which is why I used a `let`. Within the `select` both `c` and `match` are in scope, so you can use both. – Servy Mar 31 '14 at 18:17
I don't know how to define `emptyMatch` in this context. I updated my question with the code that shows what I am trying to do. Does this paint a clearer picture of what I am trying to achieve? – user1477388 Mar 31 '14 at 18:25
@user1477388 And what problems are you having with that code? – Servy Mar 31 '14 at 18:31
`HomeCountry = c.HomeCountry,` and `HostCountry = c.HostCountry,` are empty. I don't get anything for them. – user1477388 Mar 31 '14 at 18:34
1

@user1477388 Then it seems that those properties are null for those records. – Servy Mar 31 '14 at 18:35
Thank you! I have learned so much from this. I really appreciate this one. – user1477388 Mar 31 '14 at 18:48
By the way, you were right; of course. I wasn't assigning values to those name properties before. Now, all is well. – user1477388 Mar 31 '14 at 18:49

score 1 · Answer 2 · answered Mar 31 '14 at 19:36

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; }
        }

    }
}

How to perform a join on two parameters in LINQ

2 Answers2