Question

如果我能够帮助编写从表A中获取所有不动产业分类账的LINQ,以及表AB中具有价值的领域,如果图1没有进入表AB,则显示价值,否则则显示价值。

表A

AID    Field
-----------
1      OneField
2      TwoField
3      ThreeField

表B

BID    Value
-----------
1      OneValue
2      TwoValue
3      ThreeValue

表AB

ABID   AID   BID  ProfileId
-------------------------
1      1     1    1
2      2     3    1

我试图写一份Linq(或薄壳)的书,其中将显示表A中的所有数值以及表b中的价值。

e.g.

ProfileID  AID       Field        BID       Value
--------------------------------------------------------
1          1         OneField     1         OneValue
1          2         TwoField     3         ThreeValue
1          3         ThreeField   NULL      NULL

如你所知,目的是从表A中获取所有国际不动产业分类,在表AB中显示表B的价值,否则,如果表1没有进入,则显示为无效。

如果解决办法需要,我很乐意在纸浆中写,并通过储存的纸浆将其提上。

增编

Answer 1

我在经过完全自封的测试后,找到了一种行之有效的LINQ解决办法。我没有在最后添加任何说法,但“恢复”结束时的变数将包含一份名单,结果你希望。我对此进行了测试,并证实它行之有效。该守则是:

www.un.org/Depts/DGACM/index_spanish.htm Edit: 针对您的评论例2

    public class tablea
    {
        public int AID { get; set; }
        public string Field { get; set; }
    }
    public class tableb
    {
        public int BID { get; set; }
        public string Value { get; set; }
    }
    public class tableab
    {
        public int ProfileID { get; set; }
        public int AID { get; set; }
        public string Field { get; set; }
        public int BID { get; set; }
        public string Value { get; set; }
    }

    public class result
    {
        public int? profileid { get; set; }
        public int? aid { get; set; }
        public string field { get; set; }
        public string bid { get; set; }
        public string value { get; set; }
    }

    [Test]
    public void TestQuery()
    {
        var tablea = new List<tablea>();
        var tableb = new List<tableb>();
        var tableab = new List<tableab>();

        tablea.Add(new tablea{AID = 1,Field = "OneField"});
        tablea.Add(new tablea{AID = 2,Field = "TwoField"});
        tablea.Add(new tablea{AID = 3,Field = "ThreeField"});

        tableb.Add(new tableb{BID = 1,Value = "OneValue"});
        tableb.Add(new tableb{BID = 2,Value = "TwoValue"});
        tableb.Add(new tableb{BID = 3,Value = "ThreeValue"});

        tableab.Add(new tableab{AID = 1,BID=1,ProfileID = 1});
        tableab.Add(new tableab{AID = 2,BID=3,ProfileID = 1});

        var profileId = 1;

        var q1 = (from a in tablea
                  let bid = (from ab in tableab where ab.ProfileID == profileId && ab.AID == a.AID select ab.BID).FirstOrDefault()
                  let value = (from ab in tableab where ab.ProfileID == profileId && ab.AID == a.AID && ab.BID == bid select ab.Value).FirstOrDefault()
                select new result
                            {
                                profileid = profileId,
                                aid = a.AID,
                                field = a.Field,
                                bid = (bid == 0 ? "null" : bid.ToString()),
                                value = value ?? "null"
                           }).ToList();

    }

Answer 2

这一工作(未测试):

SELECT ProfileID, A.AID, Field, B.BID, Value 
FROM AB 
LEFT OUTER JOIN A ON AB.AID = A.AID
LEFT OUTER JOIN B ON AB.BID = B.BID

Answer 3

由于您希望从表A中得出所有注重成果的年度报告,你需要从表A开始:

SELECT AB.ProfileID, A.AID, A.Field, B.BID, B.Value 
FROM A 
LEFT OUTER JOIN AB ON AB.AID = A.AID
LEFT OUTER JOIN B ON AB.BID = B.BID
WHERE (AB.ProfileID = 1 OR AB.ProfileID IS NULL)

如果你在问询中开始表AB,你就只能拿到表AB中的内容,而表AB中则没有条目。

页: 1

Answer 4

[code] var result = from a in a join ab in ab on a.aid equals ab.aid into tmp1 join b in b on b.id equals ab.bid into tmp2 from c in tmp1.DefaultIfEmpty() /* this is to get the null values */ from d in tmp2.DefaultIfEmpty() select new { c.ProfileID, a.AID, a.Field,
c.bid, d.Value }; [/code]

Answer 5

卡什基文就是这样。

select TableA.*, TableB.*
from TableA
    left outer join TableC on TableA.ID = Aid
    left outer join TableB on TableB.id = Bid
where
    TableC.ProfileID = 1
    or TableC.ProfileID is null

外部加入确保你从表格中取得结果。即便在加入的另一方没有相应的记录。因此,你也必须允许在《概况》中取消价值。

Answer 6

见以下例子:

var result = from a  in a
             join ab in ab on a.aid equals ab.aid into tmp1
         join b  in b  on b.id  equals ab.bid into tmp2
         from c in tmp1.DefaultIfEmpty()  /* this is to get the null values */
         from d in tmp2.DefaultIfEmpty()
         select new 
        {
         c.ProfileID,
         a.AID,
         a.Field,   
                 c.bid,
         d.Value
        };

友情链接