none
LEFT OUTER JOINS for Two Tables RRS feed

  • Question

  • I'm looking to write a linq query that would perform like this sql:

    SELECT j.Job, d.Delivery, r.ReleaseNumber
    FROM (dsReleases.dtJobs j LEFT OUTER JOIN dsReleases.dtDeliveries d ON j.Job = d.Job)
     LEFT OUTER JOIN dsReleases.dtReleases r ON d.Delivery = r.Delivery

    If found plenty of left outer join examples, but the difference is they want to reference the third table back to the first table. I need to reference the third table to the second table.

    I got this far:

    		var results =
    				from j in dsReleases.dtJobs.AsEnumerable()
    				join d in dsReleases.dtDeliveries.AsEnumerable() 
                        on j.Job equals d.Job into tb_d
                    from y in tb_d.DefaultIfEmpty()
    				
                    join r in dsReleases.dtReleases.AsEnumerable() on y.Delivery equals r.Delivery into tb_r
    				from x in tb_r.DefaultIfEmpty()
    				select new
    				{
                        job = j.Job,
                        delivery = d.Delivery,
                        release = r.ReleaseNumber
    				};

    The error is on the second join. It says "The type of one of the expressions in the join clause is incorrect. Type inference fails in the call to 'GroupJoin.'

    I'm unable to find any examples of this type of linking and I beginning to think that maybe I should make it two queries, one nested inside the other.

    Any suggestions?

    Bernie

    Monday, March 13, 2017 9:35 PM

All replies

  • Hi BernieHunt,

    Please remove AsEnumerable method. like this:

    var results = from j in dsReleases.dtJobs
                  join d in dsReleases.dtDeliveries on j.Job equals d.Job into tb_d
                  from y in tb_d.DefaultIfEmpty()
                  join r in dsReleases.dtReleases on y.Delivery equals r.Delivery into tb_r
                  from x in tb_r.DefaultIfEmpty()
                  select new
                  {
                      job = j.Job,
                      delivery = y.Delivery,
                      release = x.ReleaseNumber
                  };

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 14, 2017 5:19 AM
    Moderator
  • Cole,

    I'm confused. I though you always had to use the AsEnumerable option on datatables. When is AsEnumerable needed or not?

    Bernie

    Wednesday, March 15, 2017 4:32 PM
  •             var results =
                        from j in dsReleases.dtJobs
                        join d in dsReleases.dtDeliveries
                            on j.Job equals d.Job into tb_d
                        from y in tb_d.DefaultIfEmpty()
                        join r in dsReleases.dtReleases on y.Delivery equals r.Delivery into tb_r
                        from x in tb_r.DefaultIfEmpty()
                        select new
                        {
                            Job = j.Job,
                            Delivery = y.Delivery,
                            PromiseDate = y.PromisedDate,
                            PromiseQuantity = y.PromisedQuantity,
                            RequestDate = y.RequestedDate,
                            ReleaseNumber = x.ReleaseNumber
                        };

    Using the above code, I get an error of

    "Object reference not set to an instance of an object."

    I have verified the dtJobs contains one record, dtDeliveries has 21 records, and dtReleasees has two records. The error was captured in the exception of a try catch block and there is no inner exception. Where do I start troubleshooting this?

    Wednesday, March 15, 2017 8:18 PM
  • Anyone?

    Tuesday, March 21, 2017 3:54 AM
  • Hi BernieHunt,

    It works well on my side with a Linq to SQL item template, Do you use Linq to SQL or ado.net entity framework. Could you please provide related data table structure.

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 21, 2017 5:33 AM
    Moderator
  • "Do you use Linq to SQL or ado.net entity framework."

    Neither, Linq to Dataset. Here is the structure of the datatset.

    <?xml version="1.0" encoding="utf-16"?>
    <xs:schema id="dsAssignReleases" targetNamespace="http://tempuri.org/dsAssignReleases.xsd" xmlns:mstns="http://tempuri.org/dsAssignReleases.xsd" xmlns="http://tempuri.org/dsAssignReleases.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified">
      <xs:element name="dsAssignReleases" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
        <xs:complexType>
          <xs:choice minOccurs="0" maxOccurs="unbounded">
            <xs:element name="dtJobs">
              <xs:complexType>
                <xs:sequence>
                  <xs:element name="Job" type="xs:string" minOccurs="0" />
                  <xs:element name="Status" type="xs:string" minOccurs="0" />
                  <xs:element name="OrderQuantity" type="xs:int" minOccurs="0" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
            <xs:element name="dtDeliveries">
              <xs:complexType>
                <xs:sequence>
                  <xs:element name="DeliveryKey" type="xs:int" minOccurs="0" />
                  <xs:element name="Delivery" type="xs:int" minOccurs="0" />
                  <xs:element name="Job" type="xs:string" minOccurs="0" />
                  <xs:element name="PromisedDate" type="xs:dateTime" minOccurs="0" />
                  <xs:element name="PromisedQuantity" type="xs:int" minOccurs="0" />
                  <xs:element name="RequestedDate" type="xs:dateTime" minOccurs="0" />
                  <xs:element name="ShippedQuantity" type="xs:int" minOccurs="0" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
            <xs:element name="dtReleases">
              <xs:complexType>
                <xs:sequence>
                  <xs:element name="ReleaseID" type="xs:int" minOccurs="0" />
                  <xs:element name="Job" type="xs:string" minOccurs="0" />
                  <xs:element name="Delivery" type="xs:int" minOccurs="0" />
                  <xs:element name="ReleaseNumber" type="xs:int" minOccurs="0" />
                  <xs:element name="StartDate" type="xs:dateTime" minOccurs="0" />
                  <xs:element name="EndDate" type="xs:dateTime" minOccurs="0" />
                  <xs:element name="ReleaseQuantity" type="xs:int" minOccurs="0" />
                  <xs:element name="Status" type="xs:string" minOccurs="0" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
          </xs:choice>
        </xs:complexType>
      </xs:element>
    </xs:schema>

    Tuesday, March 21, 2017 1:51 PM