none
LINQ: How to do conditional joining by LINQ to Object RRS feed

  • Question

  • i am doing LINQ to Object query. i have two list where i am doing joining based on few fields.

    i need to do conditional joining like when viewalllst.xFundCode start with DS then joining will be perform on frmlst.Li else frmlst.xFundCode

    i am talking about this area....see my below code

     viewalllst is not getting available for first on block for join

    on new
    {
    	val = String.IsNullOrEmpty(frmlst.Section) ? "" : frmlst.Section.Trim().ToUpper(),
    	//val1 = String.IsNullOrEmpty(frmlst.Li) ? "" : frmlst.Li.Trim().ToUpper(),
    	val1 = String.IsNullOrEmpty(frmlst.xFundCode) ? "" : frmlst.xFundCode.Trim(),
    	val2 = String.IsNullOrEmpty(frmlst.Period) ? "" : frmlst.Period.Replace("A", "").Replace("E", "").Trim().ToUpper(),
    	val3 = String.IsNullOrEmpty(frmlst.Broker) ? "" : frmlst.Broker.Trim().ToUpper()
    }

    full code

                                  var QCViewAllHistValue = (from frmlst in cfList
                                                              join viewalllst in QCViewAllBrokerList
                                                              on new
                                                              {
                                                                  val = String.IsNullOrEmpty(frmlst.Section) ? "" : frmlst.Section.Trim().ToUpper(),
                                                                  //val1 = String.IsNullOrEmpty(frmlst.Li) ? "" : frmlst.Li.Trim().ToUpper(),
                                                                  val1 = String.IsNullOrEmpty(frmlst.xFundCode) ? "" : frmlst.xFundCode.Trim(),
                                                                  val2 = String.IsNullOrEmpty(frmlst.Period) ? "" : frmlst.Period.Replace("A", "").Replace("E", "").Trim().ToUpper(),
                                                                  val3 = String.IsNullOrEmpty(frmlst.Broker) ? "" : frmlst.Broker.Trim().ToUpper()
                                                              }
                                                              equals new
                                                              {
                                                                  val = String.IsNullOrEmpty(viewalllst.ViewAllSection) ? "" : viewalllst.ViewAllSection.Trim().ToUpper(),
                                                                  //val1 = String.IsNullOrEmpty(viewalllst.ViewAllLineItem) ? "" : viewalllst.ViewAllLineItem.Trim().ToUpper(),
                                                                  val1 = String.IsNullOrEmpty(viewalllst.xFundCode) ? "" : viewalllst.xFundCode.Trim(),
                                                                  val2 = String.IsNullOrEmpty(viewalllst.ViewAllPeriod) ? "" : viewalllst.ViewAllPeriod.Replace("A", "").Replace("E", "").Trim().ToUpper(),
                                                                  val3 = String.IsNullOrEmpty(viewalllst.ViewAllBroker) ? "" : viewalllst.ViewAllBroker.Trim().ToUpper()
                                                              }
    
                                                              into tempJoin
                                                              from leftJoin in tempJoin.DefaultIfEmpty()
                                                              select new QCHelper()
                                                              {
                                                                  Broker = frmlst == null ? string.Empty : frmlst.Broker == null ? string.Empty : frmlst.Broker,
                                                                  Section = frmlst == null ? string.Empty : frmlst.Section == null ? string.Empty : frmlst.Section,
                                                                  Li = frmlst == null ? string.Empty : frmlst.Li == null ? string.Empty : frmlst.Li,
                                                                  Period = frmlst == null ? string.Empty : frmlst.Period == null ? string.Empty : frmlst.Period,
                                                                  CrossCalc1Q = frmlst == null ? string.Empty : frmlst.CrossCalc1Q == null ? string.Empty : frmlst.CrossCalc1Q,
                                                                  CrossCalc2Q = frmlst == null ? string.Empty : frmlst.CrossCalc2Q == null ? string.Empty : frmlst.CrossCalc2Q,
                                                                  CrossCalc3Q = frmlst == null ? string.Empty : frmlst.CrossCalc3Q == null ? string.Empty : frmlst.CrossCalc3Q,
                                                                  CrossCalc4Q = frmlst == null ? string.Empty : frmlst.CrossCalc4Q == null ? string.Empty : frmlst.CrossCalc4Q,
                                                                  CrossCalcFY = frmlst == null ? string.Empty : frmlst.CrossCalcFY == null ? string.Empty : frmlst.CrossCalcFY,
                                                                  Value = leftJoin == null ? string.Empty : (leftJoin.Value == null ? string.Empty : leftJoin.Value),
                                                                  
    
                                                              }).ToList<QCHelper>();

    please share some code or suggestion how to do my task. thanks

    Thursday, May 9, 2019 10:59 AM

All replies

  • This data and query is really complex. You haven't provided some of the critical information such as the structure of the types involved and the data that you expect to join. Can you provide any of this otherwise we'll just be guessing on how everything relates. It would help everybody if you could remove anything that isn't relevant to your actual question (e.g. all the properties being set that have nothing to do with the join you're attempting).

    I'd also strongly recommend that you have your underlying type(s) clean up the data rather than trying to do all this inside your LINQ query. Not only does that slow things down but it is a lot harder to read.

    class NormalizedData
    {
        public NormalizedData ( CF data )
        {
            Val = data.Section.ToUpper();
            Val1 = data.xFundCode;
            Val2 = data.Period.Replace("A", "").Replace("E", "").Trim().ToUpper();
            Val3 = data.Broker.ToUpper();
        }
    
        public NormalizedData ( Broker data )
        {
            Val = data.ViewAllSection.ToUpper();
            Val1 = data.xFundCode;
            Val2 = data.ViewAllPeriod.Replace("A", "").Replace("E", "").Trim().ToUpper();
            Val3 = data.ViewAllBroker.ToUpper();
        }
    
        public string Val { get; set; }
        public string Val1 { get; set; }
        public string Val2 { get; set; }
        public string Val3 { get; set; }
    }
    
    internal class CF
    {
        public string Section
        {
            get => _section ?? "";
            set => _section = value?.Trim();
        }
        public string Broker
        {
            get => _broker ?? "";
            set => _broker = value?.Trim();
        }
        public string Period
        {
            get => _period ?? "";
            set => _period = value?.Trim();
        }
        public string xFundCode
        {
            get => _fundCode ?? "";
            set => _fundCode = value?.Trim();
        }
        public string Li
        {
            get => _li ?? "";
            set => _li = value?.Trim();
        }
    
        private string _section, _broker, _period, _fundCode, _li;
    }
    
    internal class Broker
    {
        public string ViewAllSection
        {
            get => _allSections ?? "";
            set => _allSections = value?.Trim();
        }
        public string ViewAllPeriod
        {
            get => _allPeriod ?? "";
            set => _allPeriod = value?.Trim();
        }
        public string ViewAllBroker
        {
            get => _allBroker ?? "";
            set => _allBroker = value?.Trim();
        }
        public string xFundCode
        {
            get => _fundCode ?? "";
            set => _fundCode = value?.Trim();
        }
    
        private string _allSections, _allPeriod, _allBroker, _fundCode;
    }
    
    internal class QCHelper
    {
        public string Broker { get; set; }
        public string Section { get; set; }
        public string Li { get; set; }
        public string Period { get; set; }
    }
    
    var QCViewAllHistValue = (from frmlst in cfList
                                join viewalllst in QCViewAllBrokerList
                                on new NormalizedData(frmlst) equals new NormalizedData(viewalllst) 
                                select new QCHelper()
                                {
                                    Broker = frmlst.Broker,
                                    Section = frmlst.Section,
                                    Li = frmlst.Li,
                                    Period = frmlst.Period,
    
                                }).ToList<QCHelper>();

    Note: The above code is using expression bodies so you need at least VS 2017 to compile it.

    The downside to this is that you're creating instance data just to compare 2 types.  Other than the property names they seem to be similar types so I'd argue that syncing the names and using an interface to represent both types might be easier. Alternatively you might consider creating a composite key from the data fields so you can simply compare strings rather than creating a custom type altogether. 

    public class CF
    {
       ...
       public string GetKey ()
            {            
                var section = Section.ToUpper();
                var fundCode = xFundCode;
                var period = Period.Replace("A", "").Replace("E", "").Trim().ToUpper();
                var broker = Broker.ToUpper();
    
                return String.Join("|", section, fundCode, period, broker);
            }
    }
    
    public class Broker
    {
       …
    
       public string GetKey ()
        {
            var section = ViewAllSection.ToUpper();
            var fundCode = xFundCode;
            var period = ViewAllPeriod.Replace("A", "").Replace("E", "").Trim().ToUpper();
            var broker = ViewAllBroker.ToUpper();
    
            return String.Join("|", section, fundCode, period, broker);
        }
    }
    
    var QCViewAllHistValue = (from frmlst in cfList
                                join viewalllst in QCViewAllBrokerList
                                on frmlst.GetKey() equals viewalllst.GetKey()
                                select new QCHelper()
                                {
                                    Broker = frmlst.Broker,
                                    Section = frmlst.Section,
                                    Li = frmlst.Li,
                                    Period = frmlst.Period,
    
                                }).ToList<QCHelper>();
    With these changes in place it becomes easy to adjust the "key" based upon any rules you need.

    var fundCode = xFundCode.StartsWith("DS", StringComparison.OrdinalIgnoreCase) ? Li : xFundCode;


    Michael Taylor http://www.michaeltaylorp3.net

    Thursday, May 9, 2019 2:44 PM
    Moderator
  • Hi Sudip_inn,

    Thank you for posting here.

    >>i need to do conditional joining like when viewalllst.xFundCode start with DS then joining will be perform on frmlst.Li else frmlst.xFundCode

    Could you describe it more clearly? 

    We are waiting for your update.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 10, 2019 5:23 AM
    Moderator