outer join

Hi,

I'm having some trouble doing an outer join with Lightspeed. Here is my query:

from o in unitOfWork.FmClients

                        from i in unitOfWork.FmClientIndvls.Where(x=>x.Id == o.Id).DefaultIfEmpty()

                        where taxNos.Contains(o.TaxFileNo) 

                        select new FMClientDataNeeded

                        {

                            davcna = o.TaxFileNo,

                            emso = i == null ? "" : i.Emso,

                            imepriimek = o.ClientName,

                            maticna = o.GlobalIdType == "BRN" ? o.GlobalId : "",

                            clientNo = o.Id

                        })

However this does an inner join for some reason. Any other way I could do this? 

well, I've tried it now this way:

from a in FmClients

from i in a.FmClientIndvls.DefaultIfEmpty()

where (new[] { "99593777", "1234567" }).Contains(a.TaxFileNo)

select a.FmClientIndvls.FirstOrDefault().Emso

and I get this exception:Unable to materialize field [Title] on type [prosoft.skb.insolventnostDataAccess.FmClientIndvl]. Check your table has an Id column and that your mappings are correct. See inner exception for details. 

Index was outside the bounds of the array.

FM_CLIENT_INDVLS is like this:

describe fm_client_indvl

Describing fm_client_indvl....

NAME                            Null?     Type

------------------------------- --------- -----

CLIENT_NO                       NOT NULL  VARCHAR2(6)

TITLE                                     VARCHAR2(10)

SURNAME                         NOT NULL  VARCHAR2(20)

GIVEN_NAME_1                    NOT NULL  VARCHAR2(20)

GIVEN_NAME_2                              VARCHAR2(20)

GIVEN_NAME_3                              VARCHAR2(20)

SURNAME_FIRST                   NOT NULL  VARCHAR2(1)

SALUTATION                                VARCHAR2(20)

RESIDENT_STATUS                 NOT NULL  VARCHAR2(1)

RACE                                      VARCHAR2(2)

BIRTH_DATE                      NOT NULL  DATE

SEX                                       VARCHAR2(1)

MARITAL_STATUS                            VARCHAR2(1)

EDUCATION                                 VARCHAR2(30)

PROFESSION                                VARCHAR2(30)

FAMILY_NO                                 NUMBER(2,0)

SPECIAL_TREATMENT                         VARCHAR2(1)

SISBON                                    VARCHAR2(1)

MARKET_TO_HOME                            VARCHAR2(1)

REAL_ESTATE                               VARCHAR2(1)

PLACE_OF_BIRTH                  NOT NULL  VARCHAR2(30)

EMSO                                      VARCHAR2(13)

ZPIZ                                      VARCHAR2(10)

NO_OF_REAL_ESTATE                         NUMBER(3,0)

CADASTRAL_REGISTER_NO                     VARCHAR2(20)

INSURANCE_CARDS                           VARCHAR2(1)

PEP                                       VARCHAR2(1)

And even weirder: if I do this:

from a in FmClients

from i in a.FmClientIndvls.DefaultIfEmpty()

where (new[] { "99593777", "1234567" }).Contains(a.TaxFileNo)

select a.FmClientIndvls

I get 

Noone can help with this? :( 

how should outer joins be made in Lightspeed generally if not for this case? 

Hi Robert,

The syntax you are indicating from the query above would specify a cross join with LINQ rather than an outer join. For an outer join you need to indicate a join between two sets on a given key into a range variable and then use .DefaultIfEmpty() on that to indicate an outer join (strange indeed, but thats LINQ syntax for you!).

An example is:

from m in unitOfWork.Members
from c in unitOfWork.Comments.DefaultIfEmpty()

Indicates a cross join as there is no join key between Members and Comments

from m in unitOfWork.Members
join c in unitOfWork.Comments on m.Id equals c.MemberId into x
from y in x.DefaultIfEmpty()

Indicates a left outer join between Members and Comments on m.Id = c.MemberId, the use of DefaultIfEmpty indicates we should return nulls rather than filtering out nulls.

With the query you listed above, which build of LightSpeed 3 are you using when seeing that error? I tried putting togethor a small example which should be equivilant but it ran without error.

If you are running against the latest nightly could you provide a small repro of this behavior and either attach it to this thread or email it through to us and we can have a look at what is causing this.

Jeremy