问题描述

linq to sql 查询问题，请指教

我有三个表，A表：id,departmentcode,suppliercode
b表：departmetcode
c表“suppliercode怎么样用linq to sql 把这三个表连接起来。然后根据A表的查询条件显示呀比如A表的id=1，我一查询就出现多条记录， 我现在是想显示一条记录，然后把b表，c表的数据代出来。

解决方案

var result =from a in tableA
join b in Tableb on
a.depattmentcode equals b.code
join c in tablec on
a.suppliercode equals c.supppliercode
where a.id=1
select new { a.id,a.departmentcod,supplercode ,b.xxx,c.xxx}

解决方案二：

你应该用groupjoin

解决方案三：

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

namespace ConsoleApplication1
{
    class A
    {
        public int id { get; set; }
        public int departmentcode{ get; set; }
        public int suppliercode { get; set; }
    }
    class B
    {
        public int departmentcode { get; set; }
    }
    class C
    {
        public int suppliercode { get; set; }
    }
    class Program
    {
        static void Main(string[] args)
        {
            List<A> la = new List<A>()
            {
                new A { id = 1, departmentcode = 1, suppliercode = 1 },
                new A { id = 1, departmentcode = 1, suppliercode = 2 },
                new A { id = 1, departmentcode = 2, suppliercode = 1 },
                new A { id = 2, departmentcode = 1, suppliercode = 1 }
            };
            List<B> lb = new List<B>()
            {
                new B { departmentcode = 1 },
                new B { departmentcode = 2 }
            };
            List<C> lc = new List<C>()
            {
                new C { suppliercode = 1 },
                new C { suppliercode = 2 }
            };
            Console.WriteLine("join");
            var query1 = from a in la
                         join b in lb on a.departmentcode equals b.departmentcode
                         join c in lc on a.suppliercode equals c.suppliercode
                         where a.id == 1
                         select new { a.id, a.suppliercode, a.departmentcode };
            foreach (var item in query1)
                Console.WriteLine(item);
            Console.WriteLine("group join");

            var query2 = from a in la
                         join b in lb on a.departmentcode equals b.departmentcode
                         join c in lc on a.suppliercode equals c.suppliercode
                         where a.id == 1
                         group a by a.id into g
                         select new { a = g.Key, b = g.Select(x => x.departmentcode), c = g.Select(x => x.suppliercode) };
            foreach (var item in query2)
            {
                Console.WriteLine(item.a);
                foreach (var b in item.b)
                    Console.WriteLine("t" + b);
                foreach (var c in item.c)
                    Console.WriteLine("t" + c);
            }

        }
    }
}

解决方案四：

join
{ id = 1, suppliercode = 1, departmentcode = 1 }
{ id = 1, suppliercode = 2, departmentcode = 1 }
{ id = 1, suppliercode = 1, departmentcode = 2 }
group join
1
1
1
2
1
2
1
Press any key to continue . . .

解决方案五：

我也是按这个来写的。为什么反回的时候是两条相同的数据呀