Skip to main content

LINQ Update List with JOIN with AND Condition

LINQ Update List using JOIN with AND Condition

There is a direct way to update a collection list by joining with another but that is based on only one column and which will be ultimate thing to JOIN on. But how we can update if we need to compare more than one column ? We can join using AND query for which we need to join with first column and then where clause with second column to update. Below is detailed example for JOIN and Update based on 2 columns. Demo of same is given below. 

Classes definition 

  public class clsPlayers

    {

        public int intId { get; set; } = 0;

        public string strPlayerName { get; set; } = "";

        public string strPosition { get; set; } = "";

        public string strRole { get; set; } = "";

        public string strGrade { get; set; } = "";

    }


    public class clsPositions

    {

        public string strGrade { get; set; } = "";

        public string strPosition { get; set; } = "";

        public string strRole { get; set; } = "";

    }

Adding data to classes

        var objclsPlayers = new List<Models.clsPlayers>();

            objclsPlayers.Add(new Models.clsPlayers

            {

                intId = 1,

                strGrade = "E",

                strRole = "Bat",

                strPlayerName = "Virat",

                strPosition = "One Down"

            });

            objclsPlayers.Add(new Models.clsPlayers

            {

                intId = 2,

                strGrade = "E",

                strRole = "Bat",

                strPlayerName = "Rohit",

                strPosition = "Opening"

            });

            objclsPlayers.Add(new Models.clsPlayers

            {

                intId = 3,

                strGrade = "E",

                strRole = "Bat",

                strPlayerName = "Rahul",

                strPosition = "Opening"

            });

            objclsPlayers.Add(new Models.clsPlayers

            {

                intId = 4,

                strGrade = "E",

                strRole = "Ball",

                strPlayerName = "Kuldip",

                strPosition = "Spinner"

            });

            objclsPlayers.Add(new Models.clsPlayers

            {

                intId = 5,

                strGrade = "E",

                strRole = "Ball",

                strPlayerName = "Kumar",

                strPosition = "Fast"

            });


            var objclsPositions = new List<Models.clsPositions>();

            objclsPositions.Add(new Models.clsPositions

            {

                strGrade = "A",

                strRole = "Bat",

                strPosition = "One Down"

            });

            objclsPositions.Add(new Models.clsPositions

            {

                strGrade = "B",

                strRole = "Bat",

                strPosition = "Opening"

            });


            objclsPositions.Add(new Models.clsPositions

            {

                strGrade = "C",

                strRole = "Ball",

                strPosition = "Spinner"

            });


            objclsPositions.Add(new Models.clsPositions

            {

                strGrade = "D",

                strRole = "Ball",

                strPosition = "Fast"

            });

Here we need to update first collection based on JOIN  with 2 columns i.e. strPosition and strRole and finally strGrade values will be updated. The Solution... 

   foreach (var NewData in objclsPlayers.Join(objclsPositions,
             Players => Players.strPosition, Positions => Positions.strPosition,
             (Players, Positions) => new { Players, Positions })
             .Where(combine => {
                 return combine.Players.strRole == combine.Positions.strRole; }))
            {
                NewData.Players.strGrade = NewData.Positions.strGrade;
            }

Assuming this as a Web API code, Output in Browser will be,

<ArrayOfclsPlayers...>

<clsPlayers>

<strGrade>A</strGrade>

<intId>1</intId>
<strPlayerName>Virat</strPlayerName>

<strPosition>One Down</strPosition>

<strRole>Bat</strRole>

</clsPlayers>


<clsPlayers>

<strGrade>B</strGrade>

<intId>2</intId>
<strPlayerName>Rohit</strPlayerName>

<strPosition>Opening</strPosition>

<strRole>Bat</strRole>

</clsPlayers>


<clsPlayers>

<strGrade>B</strGrade>

<intId>3</intId>
<strPlayerName>Rahul</strPlayerName>

<strPosition>Opening</strPosition>

<strRole>Bat</strRole>

</clsPlayers>


<clsPlayers>

<strGrade>C</strGrade>

<intId>4</intId>
<strPlayerName>Kuldip</strPlayerName>

<strPosition>Spinner</strPosition>

<strRole>Ball</strRole>

</clsPlayers>


<clsPlayers>

<strGrade>D</strGrade>

<intId>5</intId>
<strPlayerName>Kumar</strPlayerName>

<strPosition>Fast</strPosition>

<strRole>Ball</strRole>

</clsPlayers>

</ArrayOfclsPlayers>

Popular posts from this blog

.NET Core and MongoDB generic library

Hello all, you all must have come across lot of code suggestions, answers for your various queries regarding reusable libraries of server side languages and NoSql Databases. But many of those suggestions have code which is specific to that Database, tables, collections. How about writing library which is dynamic and can work with any collection, any field to query upon ? Yes below is an example of .Net Core and MongoDB reusable dynamic library which is generic and you can use it with any collection. It can be the base for your .net core mongodb generic repository. Please note that this is not for how to fetch values from config cause you know that better than me. This place is to discuss Coding, Sql, NoSql, Technology, Nuclear Warfare and many more and not to repeat what we have already. So let's start learning, Server side language : C# Framework : .Net Core 2.1 Database : MongoDB MongoDB Database first, > use PlayersDB switched to db PlayersDB > show tables tb...
Read more

Interface vs abstract class

Many of you are still in confusion that when I should use Interface and when Abstract Class while developing applications or for preparing for an interviews. As these are part of core concepts of Object oriented programming languages, all of us should know exact use case. As you all know bookish definitions of both concepts, we will jump to simple example directly to explain the actual use cases of both. Imagine you are working on a system which is representing list of Colleges which will have Engineering Colleges, Medical Colleges and regular degree colleges. Now there are few things which are must when it comes to any college like Black(green) Board, Classrooms which we can have in our Interface, it will be implemented later based on types of colleges i.e. Classes which will be implementing that Interface. But if all the colleges are having this then what does Abstract Class does ?? Big question right.. Here is the answer of that, Abstract class is for some common behavior t...
Read more