多表使用LINQ返回任何值 [英] Multiple table Join using LINQ returning no values

问题描述

我有三张表如下：

Table A
Id
Divi_code
Unit_code 
Other column


Table B
Divi_code
Divi_name

Table c
Unit_code
Unit_name

我有一个从表A生成的列表，使用某些条件，列表项可能或不可能有divi_code / unit_code值。我现在需要找到divi_name / unit_name，如果列表包含divi_code / unit_code的任何值。我正在做这个coz我不想发送divi /单元代码到UI，我想发送名称。

I have a list generated from table A using some where condition, the list item may or may not have divi_code/unit_code values. I now need to find the divi_name/unit_name if the list contains any value for divi_code/unit_code. I'm doing this coz I don't want to send divi/unit code to the UI, I want send the names.

我试图加入这些表，如bellow ：

I tried to join these table like bellow:

var list = (from s in DataContext.HRM_HLDY_SPCL_FOR.AsEnumerable()
                    where s.HLDY_DATE == Convert.ToDateTime(Date)

                    join dv in DataContext.HRM_DIVISION on s.DIVI_CODE equals dv.DIVI_CODE

                    join un in DataContext.HRM_UNIT on s.UNIT_CODE equals un.UNIT_CODE

                    select new HRM_HLDY_SPCL_FORModel { HLDY_SPCL_SLNO = (s.HLDY_SPCL_SLNO).ToString(), DIVI_NAME= dv.DIVI_NAME, UNIT_NAME= un.UNIT_NAME, ActiveStatus= s.ACTIVE_STATUS, HLDY_SPCL_REM= s.HLDY_SPCL_REM}).ToList();

我的查询只面向那些具有divi_code和amp unit_code。即使没有divi_code / unit_code，我也希望在列表中包含所有数据。在这种情况下，divi_name / unit_name的null将足够好。

My query only facing those data which have both divi_code & unit_code. I would like to have all the data in my list even if it don't have divi_code/unit_code. In that case null for divi_name/unit_name would be good enough.

推荐答案

我想你正在描述一个在linq中加入。有不同的方式来实现这一点。我最喜欢这种方式很明显，这是一个左连接：

I think you are describing a left join in linq. There are diffrent ways to accomplish that. I like this way the most. It is clear that it is a left join:

var list = (from s in DataContext.HRM_HLDY_SPCL_FOR.AsEnumerable()

                from dv in DataContext.HRM_DIVISION
                    .Where(d=>d.DIVI_CODE==s.DIVI_CODE).DefaultIfEmpty()

                from un in DataContext.HRM_UNIT 
                    .Where(u=>u.UNIT_CODE== s.UNIT_CODE).DefaultIfEmpty()
                where s.HLDY_DATE == Convert.ToDateTime(Date)
                select new HRM_HLDY_SPCL_FORModel 
                { 
                    HLDY_SPCL_SLNO = (s.HLDY_SPCL_SLNO).ToString(), 
                    DIVI_NAME= (dv==null?null:dv.DIVI_NAME), 
                    UNIT_NAME= (un==null?null:un.UNIT_NAME), 
                    ActiveStatus= s.ACTIVE_STATUS, 
                    HLDY_SPCL_REM= s.HLDY_SPCL_REM
                }
                ).ToList();

这篇关于多表使用LINQ返回任何值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！