C# LINQ(Select, Oderby, Group, Join)

• LINQ(Language-Integrated Query)

- 쿼리 기능

- from : 어디에서 찾을 것인지(from 범위 변수 in 데이터 원본)

- where : 조건이 무엇인지(where 조건식)

- select : 어떤 것을 가져올 것인지(select 범위 변수)

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

namespace _125_LINQ
{
    class Program
    {
        static void Main(string[] args)
        {
            int[] data = new int[] { 0, 54, 98, 102, 332 };

            var QueryData = // IEnumerable<int> QueryData =
                from temp in data
                where temp < 100  //if(temp < 100)
                select temp;

            var test = from temp in data
                       select temp;

            foreach(int n in QueryData)
            {
                Console.WriteLine("QueryData : " + n);
            }

            //람다식 사용과 비교
            List<int> listData = new List<int>(data);
            List<int> findAllData = listData.FindAll(a => a < 100);

            foreach(int n in findAllData)
            {
                Console.WriteLine("findAllData : " + n);
            }
        }
    }
}

 

• select

- 결과를 선택

- LINQ 쿼리식 끝나는 부분

- 특정 형식으로 변환 가능

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

namespace _126_select
{
    struct Student
    {
        public int _id;
        public string _name;
        public int _kor;
        public int _eng;

        public Student(int _id, string _name, int _kor, int _eng)
        {
            this._id = _id;
            this._name = _name;
            this._kor = _kor;
            this._eng = _eng;
        }
    }
    class Program
    {
        static void Main(string[] args)
        {
            Student[] arrStudetns =
            {
                new Student(){_id = 100, _name = "John", _kor = 100, _eng = 20},
                new Student(){_id = 200, _name = "Jane", _kor = 80, _eng = 20},
                new Student(300, "Tom", 50, 60),
                new Student(400, "Max", 80, 80),
                new Student(500, "Jack", 70, 70),
            };

            var QueryData =
                from data in arrStudetns
                where data._id > 200 && data._kor > 50
                select new
                {
                    id = data._id,
                    name = data._name,
                    total = data._kor + data._eng
                };
            foreach(var data in QueryData)
            {
                Console.WriteLine("data.id : " + data.id);
                Console.WriteLine("data.name : " + data.name);
                Console.WriteLine("data.total : " + data.total);
                Console.WriteLine();
            }
        }
    }
}

 

• orderby

- 데이터 정렬

- ascending 키워드(오름차순)

- descending 키워드(내림차순)

- "," 컴마로 둘 이상의 데이터 정렬

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

namespace _126_select
{
    struct Student
    {
        public int _id;
        public string _name;
        public int _kor;
        public int _eng;

        public Student(int _id, string _name, int _kor, int _eng)
        {
            this._id = _id;
            this._name = _name;
            this._kor = _kor;
            this._eng = _eng;
        }
    }
    class Program
    {
        static void Main(string[] args)
        {
            Student[] arrStudetns =
            {
                new Student(){_id = 100, _name = "John", _kor = 100, _eng = 20},
                new Student(){_id = 200, _name = "Jane", _kor = 80, _eng = 20},
                new Student(300, "Tom", 50, 60),
                new Student(400, "Max", 80, 80),
                new Student(500, "Jack", 70, 70),
            };

            var QueryData =
                from data in arrStudetns
                orderby (data._kor + data._eng) descending  //내림차순 정렬
                //orderby (data._kor + data._eng) ascending  //내림차순 정렬
                select data;

            foreach (var data in QueryData)
            {
                Console.Write(" _id : " + data._id + " _name : " + data._name);
                Console.WriteLine(" _total : " + (data._kor + data._eng));
            }
        }
    }
}

 

• group

- 데이터 분류 후 그룹화

- 문법 : group A by B into C

- A : 범위, B : 분류 기준, C : 그룹 변수

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

namespace _126_select
{
    struct Student
    {
        public int _id;
        public string _name;
        public int _kor;
        public int _eng;

        public Student(int _id, string _name, int _kor, int _eng)
        {
            this._id = _id;
            this._name = _name;
            this._kor = _kor;
            this._eng = _eng;
        }
    }
    class Program
    {
        static void Main(string[] args)
        {
            Student[] arrStudetns =
            {
                new Student(){_id = 100, _name = "John", _kor = 100, _eng = 20},
                new Student(){_id = 200, _name = "Jane", _kor = 80, _eng = 20},
                new Student(300, "Tom", 50, 60),
                new Student(400, "Max", 80, 80),
                new Student(500, "Jack", 70, 70),
            };

            var QueryData =
                from data in arrStudetns
                orderby (data._kor + data._eng) descending  //내림차순 정렬
                group data by (data._eng + data._kor) < 150;

            foreach (var data in QueryData)
            {
                string str = data.Key ? "합이 150보다 작은 경우 : " : "합이 150보다 큰 경우 : ";
                Console.WriteLine(str);

                foreach (var item in data)
                    Console.WriteLine("\t{0} : {1}", item._name, (item._kor + item._eng));
            }
        }
    }
}

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

namespace _126_select
{
    struct Student
    {
        public int _id;
        public string _name;
        public int _kor;
        public int _eng;

        public Student(int _id, string _name, int _kor, int _eng)
        {
            this._id = _id;
            this._name = _name;
            this._kor = _kor;
            this._eng = _eng;
        }
    }
    class Program
    {
        static void Main(string[] args)
        {
            Student[] arrStudetns =
            {
                new Student(){_id = 100, _name = "John", _kor = 100, _eng = 20},
                new Student(){_id = 200, _name = "Jane", _kor = 80, _eng = 20},
                new Student(300, "Tom", 50, 60),
                new Student(400, "Max", 80, 80),
                new Student(500, "Jack", 70, 70),
            };

            var QueryData =
                from data in arrStudetns
                group data by ((data._eng + data._kor) / 2) / 10 into gTemp
                orderby gTemp.Key ascending
                select gTemp;

            foreach (var data in QueryData)
            {
                Console.WriteLine("data : {0} 에서 {1}", data.Key * 10, (data.Key + 1) * 10);
                foreach (var item in data)
                {
                    Console.WriteLine("\t name : {0}, avg : {1}", item._name, (item._kor + item._eng) / 2f);
                }
            }
        }
    }
}

 

• join

- 두 개의 데이터를 연결

- 내부조인

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

namespace _126_select
{
    struct Student
    {
        public int _id;
        public string _name;
        public int _kor;
        public int _eng;

        public Student(int _id, string _name, int _kor, int _eng)
        {
            this._id = _id;
            this._name = _name;
            this._kor = _kor;
            this._eng = _eng;
        }
    }
    struct Detail
    {
        public string _name;
        public int gender;
    }
    class Program
    {
        static void Main(string[] args)
        {
            Student[] arrStudetns =
            {
                new Student(){_id = 100, _name = "John", _kor = 100, _eng = 20},
                new Student(){_id = 200, _name = "Jane", _kor = 80, _eng = 20},
                new Student(300, "Tom", 50, 60),
                new Student(400, "Max", 80, 80),
                new Student(500, "Jack", 70, 70),
            };

            Detail[] arrDetails =
            {
                new Detail(){_name = "John", gender = 1},
                new Detail(){_name = "Jane", gender = 0},
                new Detail(){_name = "Juliet", gender = 0},
                new Detail(){_name = "Max", gender = 1},
                new Detail(){_name = "Jack", gender = 1},
            };

            var QueryData =
                from data in arrStudetns
                join detail in arrDetails on data._name equals detail._name
                select new
                {
                    name = data._name,
                    total = data._eng + data._kor,
                    gender = (detail.gender == 0) ? "여자" : "남자"
                };                

            foreach (var item in QueryData)
            {
                Console.WriteLine("name : " + item.name);
                Console.WriteLine("gender : " + item.gender);
                Console.WriteLine("total : " + item.total);

                Console.WriteLine();
            }
        }
    }
}

- 외부조인

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

namespace _126_select
{
    struct Student
    {
        public int _id;
        public string _name;
        public int _kor;
        public int _eng;

        public Student(int _id, string _name, int _kor, int _eng)
        {
            this._id = _id;
            this._name = _name;
            this._kor = _kor;
            this._eng = _eng;
        }
    }
    struct Detail
    {
        public string _name;
        public int gender;
    }
    class Program
    {
        static void Main(string[] args)
        {
            Student[] arrStudetns =
            {
                new Student(){_id = 100, _name = "John", _kor = 100, _eng = 20},
                new Student(){_id = 200, _name = "Jane", _kor = 80, _eng = 20},
                new Student(300, "Tom", 50, 60),
                new Student(400, "Max", 80, 80),
                new Student(500, "Jack", 70, 70),
            };

            Detail[] arrDetails =
            {
                new Detail(){_name = "John", gender = 1},
                new Detail(){_name = "Jane", gender = 0},
                new Detail(){_name = "Juliet", gender = 0},
                new Detail(){_name = "Max", gender = 1},
                new Detail(){_name = "Jack", gender = 1},
            };

            var QueryData =
                from data in arrStudetns
                join detail in arrDetails on data._name equals detail._name into inData
                from detail in inData.DefaultIfEmpty(new Detail() { gender = 1})
                select new
                {
                    name = data._name,
                    total = data._eng + data._kor,
                    gender = (detail.gender == 0) ? "여자" : "남자"
                };

            foreach (var item in QueryData)
            {
                Console.WriteLine("name : " + item.name);
                Console.WriteLine("gender : " + item.gender);
                Console.WriteLine("total : " + item.total);

                Console.WriteLine();
            }
        }
    }
}