How to write the following in Linq or Sql

Question

Hi,

Appreciate if I can get some help writing a LINQ that will get ALL FIELDS from table A, and those fields for which profile 1 has a value in table AB, show the value, otherwise if profile 1 has no entry in table AB, then show value as null.

Table A

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

Table B

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

Table AB

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

I'm trying to write a Linq (or sql query) that will show me for profile id 1, all the values from table a, and for those that apply, the value from table b.

e.g.

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

As you can see, the aim is to get ALL FIELDS from table A, and those fields for which profile 1 has an entry in table AB, show the value from table B, otherwise if profile 1 has no entry in table AB, then show as null.

If the solution requires it, I am happy to write that in sql and call it via stored proc.

Thanks

Answer 1

This should work (not tested):

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 2

Since you want ALL ROWS from table A, you need to start with table 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)

If you start with table AB in your query, you'll only ever get what's in table AB - and row #3 has no entry in table AB.

Marc

Answer 3

Hi

[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 4

Hi

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
     };

Answer 5

I got a working LINQ solution with a full self contained test. I did not add any asserts at the end but the variable at the end "queryResult" will contain a list with the result you desire. I tested it and verified that it works. Here is the code:

Edit: In response to your comment Here is example #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 6

The SQL version looks like this

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

The outer join ensures you get results from TableA even if there are no matching records on the other side of the join. You have to allow for null values in ProfileID too as a result.