LINQ连接查询

用LINQ进行连接查询，感觉挺别扭，还是用SQL比较舒服，故记录一下，以备后用。

先把数据列出来

    class Pet
    {
        public string PetName { get; set; }
        public string OwnerName { get; set; }
    }

    class Person
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

 List<Person> persons = new List<Person>()
        {
            new Person(){FirstName="Zhang",LastName="San"},
            new Person(){FirstName="Li",LastName="Si"},
            new Person(){FirstName="Wang",LastName="Wu"},
            new Person(){FirstName="Zhao",LastName="Liu"}
            
        };

        List<Pet> pets = new List<Pet>() 
        { 
            new Pet(){PetName="Cat",OwnerName="Zhang"},
            new Pet(){PetName="Dog",OwnerName="Si"},
            new Pet(){PetName="Monkey",OwnerName="Wang"},
            new Pet(){PetName="Panda",OwnerName="Liu"},
            new Pet(){PetName="King Kong"}
        };

 

来一个最简单的做链接查询与右连接查询：

 左连接

var p = from person in persons
                    join pet in pets
                    on person.FirstName equals pet.OwnerName into petsOrEmpty
                    from pet in petsOrEmpty.DefaultIfEmpty()
                    select new
                    {
                        FirstName = person.FirstName,
                        LastName = person.LastName,
                        PetName = pet == null ? string.Empty : pet.PetName

                    };
            foreach (var i in p)
                Console.WriteLine("{0} {1}:{2}", i.FirstName, i.LastName, i.PetName);

            //结果为：
            //Zhang San:Cat
            //Li Si:
            //Wang Wu:Monkey
            //Zhao Liu:

右连接

var p = from pet in pets
                    join person in persons
                    on pet.OwnerName equals person.FirstName into perdonsOrEmpty
                    from person in perdonsOrEmpty.DefaultIfEmpty()
                    select new
                    {
                        FirstName = person == null ? string.Empty : person.FirstName,
                        LastName = person == null ? string.Empty : person.LastName,
                        PetName = pet.PetName

                    };

            foreach (var i in p)
                Console.WriteLine("{0} {1}:{2}", i.FirstName, i.LastName, i.PetName);

            //结果为：
            //Zhang San:Cat
            // :Dog
            //Wang Wu:Monkey
            // :Panda
            // :King Kong

下面列举的连接条件不是一个单纯的相等条件，会带有AND 和 OR

带AND的连接

var p = from person in persons
                    join pet in pets
                    on new { FirstName = person.FirstName, LastName = person.LastName } equals 
　　　　　　　　　　　　　　new　{ FirstName = pet.OwnerName, LastName = pet.OwnerName } into petsOrEmpty
                    from pet in petsOrEmpty.DefaultIfEmpty()
                    select new
                    {
                        FirstName = person.FirstName,
                        LastName = person.LastName,
                        PetName = pet == null ? string.Empty : pet.PetName

                    };
            foreach (var i in p)
                Console.WriteLine("{0} {1}:{2}", i.FirstName, i.LastName, i.PetName);

            //结果为：
            //Zhang San:
            //Li Si:
            //Wang Wu:
            //Zhao Liu:

下面这个例子个人觉得不是没用到连接，但也能达到连接的效果，若有哪位大师知道真正带OR的连接查询，请指点

var p = from person in persons
                    from pet in pets
                    .Where(inner => person.LastName == inner.OwnerName ||
　　　　　　　　　　　　　　　　 person.FirstName == inner.OwnerName).DefaultIfEmpty()
                    select new
                    {
                        FirstName = person.FirstName,
                        LastName = person.LastName,
                        PetName = pet.PetName
                    };
            foreach (var i in p)
                Console.WriteLine("{0} {1}:{2}", i.FirstName, i.LastName, i.PetName);

            //结果为：
            //Zhang San:Cat
            //Li Si:Dog
            //Wang Wu:Monkey
            //Zhao Liu:Panda