首页 新闻 赞助 找找看

.net 4.0 linq左连接问题(急)

0
悬赏园豆:50 [待解决问题] 解决于 2011-05-04 18:47

select * from table1

left outer join

(

select field,Sum(JE) As JE from table2 Group By field

) as table2

on table1.field2 = table2.field

这段sql如何用linq编写?

MyPro的主页 MyPro | 初学一级 | 园豆:150
提问于:2011-04-19 18:47
< >
分享
所有回答(3)
0

var query = from table1 in context.table1
                join table2 in context.table2 on table1.id equals table2.id into table

                select new { entity.field1, entity.field2,... };

类似这样,自己做个测试比较好,拿这个编辑器跟visual studio的智能感知去较劲,简直找死啊~~~

谢谢理解~~~

DYStudio.Net | 园豆:1747 (小虾三级) | 2011-04-19 20:14
visual studio的智能感知~~?具体说明一下呗,我才用这个,嘿嘿
支持(0) 反对(0) MyPro | 园豆:150 (初学一级) | 2011-04-21 18:36
0

在dbml里面可以右键,Add-> Assiociation(中文版的不晓得,我用的英文的), 自己映射好主键和外键, 就可以在两个DataModel之间建立一个连接,然后你就可以用类似contextTable1.Table2.field1....这样的表达了。

草头蕾 | 园豆:210 (菜鸟二级) | 2011-04-20 05:12
0
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
    #region Department
    public class Department
    {
        public Department() { }

        public Department(string id, string name)
        {
            this.id = id;
            this.deptName = name;
        }

        private string id;

        public string Id
        {
            get { return this.id; }
            set { this.id = value; }
        }

        private string deptName;

        public string DeptName
        {
            get { return this.deptName; }
            set { this.deptName = value; }
        }

    }
    #endregion

    #region Position
    public class Position
    {
        public Position() { }

        public Position(string id, string name)
        {
            this.id = id;
            this.pName = name;
        }

        private string id;

        public string Id
        {
            get { return this.id; }
            set { this.id = value; }
        }

        private string pName;

        public string PName
        {
            get { return this.pName; }
            set { this.pName = value; }
        }

    }
    #endregion

    #region Employee
    public class Employee
    {
        public Employee() { }

        public Employee(string id, string name, string deptId)
        {
            this.id = id;
            this.name = name;
            this.deptId = deptId;
        }

        public Employee(string id, string name, string deptId, string pId)
        {
            this.id = id;
            this.name = name;
            this.deptId = deptId;
            this.positionId = pId;
        }

        private string id;

        public string Id
        {
            get { return this.id; }
            set { this.id = value; }
        }

        private string name;

        public string Name
        {
            get { return this.name; }
            set { this.name = value; }
        }

        private string deptId;

        public string DeptId
        {
            get { return this.deptId; }
            set { this.deptId = value; }
        }

        private string positionId;

        public string PositionId
        {
            get { return this.positionId; }
            set { this.positionId = value; }
        }

    }
    #endregion

    #region main
    class Program
    {
        static void Main(string[] args)
        {
            Employee[] em =
            {
                new Employee("1", "张三", "1", "1"),
                new Employee("2", "李三", null, "1"),
                new Employee("3", "王三", "2", null),
                new Employee("4", "赵三", "2", "2")
            };

            Department[] dept = 
            {
                new Department("1", "部门1"),
                new Department("2", "部门2")
            };

            Position[] position = 
            {
                new Position("1", "职位1"),
                new Position("2", "职位2")
            };

            var q1 =
                from em1 in em
                join dept1 in dept on em1.DeptId equals dept1.Id into joinedDept
                from dept2 in joinedDept.DefaultIfEmpty()
                join p1 in position on em1.PositionId equals p1.Id into joinedPosition
                from p2 in joinedPosition.DefaultIfEmpty()

                select new
                {
                    em1.Id,
                    em1.Name,
                    DN = dept2 != null ? dept2.DeptName : null,
                    PS = p2 != null ? p2.PName : null
                };

            foreach (var obj in q1)
            {
                System.Console.WriteLine("{0}", obj);
            }
        }

    }
    #endregion
}
zingier | 园豆:205 (菜鸟二级) | 2011-09-12 18:06
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册