BasharAbuShamaa BasharAbuShamaa - 3 months ago 11
C# Question

Inner join not working when use equal with %, What an alternative way to use it like like %

I have Medals class, I call a service that return all Medal class fields except for the two fields ArabicName and ISOCode; Which I have to bring them from another table class "CountrysTBLs" , I made this join code:

The Medal class:

public class Medals {
public int bronze_count { get; set; }
public string country_name { get; set; }
public int gold_count { get; set; }
public string id { get; set; }
public int place { get; set; }
public int silver_count { get; set; }
public int total_count { get; set; }
public string ArabicName { get; set; } // this field not return by service
public string ISOCode { get; set; } // this field not return by service
}


The code:

var cntrs = db.CountrysTBLs.ToList();
List<Medals> objs = call_Service_Of_Medals_Without_ISOCode();

IEnumerable<Medals> qury = from obj in objs
join cntry in cntrs on obj.country_name equals '%' + cntry.CommonName + '%'
select new Medals
{
ArabicName = cntry.ArabicName,
ISOCode = cntry.ISOCode,
country_name = obj.country_name,
place = obj.place,
gold_count = obj.gold_count,
silver_count = obj.silver_count,
bronze_count = obj.bronze_count,
total_count = obj.total_count
};


I got no result?!
How to fix that? Is there is any way to bring the two fields(ISOCode, ArabicName) without even use the inner join, and in same time best performance?

Answer

You want something like this to achieve LIKE functionality

        List<Medals> medals = new List<Medals>();

        var list = medals.Select(x => new
        {
            obj = x,
            country = countries.FirstOrDefault(c => c.CommonName.Contains(x.country_name))
        });

or something like this (if you want to just enrich each medal)

        foreach (var medal in medals)
        {
            var country = countries.FirstOrDefault(c => c.CommonName.Contains(x.country_name));

            medal.ISOCode = country.ISOCode;
            medal.ArabicName = country.ArabicName;
        }

Do note that this is not as performant as a Dictionary<string,Coutnry> of countries where the key is the country name, but as you need a LIKE comparison you would need to bring in a custom data structure such as Lucene index for fast lookups. But check first, if the lists are small enough, it probably won't be a problem. Otherwise, why not make the Medal.Country_Name and Country.Name the same? So you can do quick Dictionary (hashtable) lookups

Comments