Left Join

[Alfred Ortega]

When I try to do a left join, I'm only getting the empty rows, not both.  Is this fully supported yet?

Thanks,
Al

[Thomas]

Hi Alfred Ortega,

currently joins are not fully support yet, but they are still on our roadmap for LINQ support. Which kind of query you want to express, maybe there is an easier / more object oriented way to do that?

Greetings,
Thomas
the Telerik team

Check out Telerik Trainer , the state of the art learning tool for Telerik products.

[Alfred Ortega]

Thomas,
Thanks for the answer, what I'm trying to do is pretty simple actually.  I have two table which to keep things simple I'll call Guy and Wife.
Guy
ID int identity(1,1) --Primary Key
...other fields.

Wife
ID int identity(1,1) -- Primary Key
HusbandID int --Foreign Key back to Guy
..other fields.

All pretty much normal stuff, nothing complex and of course there is a class mapped to each table named Guy and Wife.  Now some Guy's have wives, some do not which is why the standard join doesn't work for me since then Guys without wives records don't show up - and I kind of need them to show up too ;-) 

As you may have guessed If there is a wife- there will be one and only one wife record to which a link will then appear to view the wife record.  Of course I don't really have guys/wives of course but the relationship is a 0-1 it's really about how to accomplish a left/right/outer or any non-equals join.

Thanks in advance,
Al 

[Thomas]

Hello Alfred,

using an ORM can be really helpful and fun in this case :

var marriedGuys = from g in scope.Extent<Guy>() where g.Wife != null select g;
var loneGuys = from g in scope.Extent<Guy>() where g.Wife == null select g;
var allGuys = from g in scope.Extent<Guy>() select g;
var cuckoldedGuys = from g in scope.Extent<Guy>() where g.Wide.Husband != g select g;

No need to join on id by yourself, when there is a reference field: OpenAccess will generate the right joins for you.

Best wishes,
Thomas
the Telerik team

Check out Telerik Trainer , the state of the art learning tool for Telerik products.

[Alfred Ortega]

Okay but what about when i want to get records  x to  y for a given "page"?  With a Linq Join I could do a Skip(x).Take(10) and just get the records I want fairly easy.  I think with your way I'd end up doing unions then selecting the specific records from that.

Al

[Thomas]

Hello Alfred ,

that can also be done:

var marriedGuys = from g in scope.Extent<Guy>() where g.Wife != null select g;
marriedGuys.Skip(100);
marriedGuys.Take(20);

That would give you only #101-120 from the entire result.

All the best,
Thomas
the Telerik team

Check out Telerik Trainer , the state of the art learning tool for Telerik products.

[Alfred Ortega]

That is not a left join, I still need a left outer join as described at: http://msdn.microsoft.com/en-us/library/bb397895.aspx

var q = (from g in objectScope.Extent<Guy>()
        join w in objectScope.Extent<Wife>() on g.Id equals w.HusbandId into g
        from o in g.DefaultIfEmpty()
        select new {GuyName = c.Name, WifeName = o == null ? "Single" : o.WifeName}).Skip(10).Take(10);

Now I can page through the set of records whether or not wives exist or not.  However when I execute the above not all the records are showing up, only the single guys which isn't what I need. 


Al

[Thomas]

Hi Alfred,

 public void JoinLeftOuter()
        {
            var ext = scope.Extent<Guy>();
            var query = from person in ext
                        join s in scope.Extent<Wife>() on person equals s.Spouse into gj
                        from x in gj.DefaultIfEmpty()
                        select new { person.Name, SpouseName = (x == null ? String.Empty : x.Name)};
            var l = query.ToList();
        }

The join is beeing done in memory currently, as we are not yet able to push that to the server but the result is correct ...

Best wishes,
Thomas
the Telerik team

Check out Telerik Trainer , the state of the art learning tool for Telerik products.

[Alfred Ortega]

Thanks for your assistance, hopefully this will implemented server side soon.

Al

[Thomas]

Hello Alfred,

I found a way how you can express it without waiting.... :-)
Do it the like

     var ext = scope.Extent<Guy>();
            var query = from person in ext
                        select new { Name = person.Name, SpouseName = person.Spouse.Name };

because in the projection a left join is used, whereas in the filter an inner join is required.

Sincerely yours,
Thomas
the Telerik team

Check out Telerik Trainer , the state of the art learning tool for Telerik products.

[Alfred Ortega]

Would that work with if it started:

var ext = scope.Extent<Guy>().Skip(10).Take(10);

[Thomas]

Hi Alfred,

Skip and Take are supported, but you should use them after the projection clause.

Kind regards,
Thomas
the Telerik team

Check out Telerik Trainer , the state of the art learning tool for Telerik products.

[Alfred Ortega]

Thanks for the update and I'll keep that in mind.  I do hope that maybe Q2 or Q3 will have this functionality though.

[jon]

When I try to do this I get this error:
  CS1941: The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'GroupJoin'.
Source Error:

Line 34: var ext = scope.Extent<Cage>(); Line 35: var result = from c in ext Line 36: join a in scope.Extent<Animal>() on c equals a.IdCage1 into cageanimals Line 37: from ca in cageanimals.DefaultIfEmpty() Line 38: select new{...}

Ideas?

[Thomas]

Hi jon,

this is a csc compiler error that means in this case: the join condition is inconsistent with the types involved. A cage instance is compared to the IdCage1 field, probably just an int. If both sides use int or object, that error should be gone.

Greetings,
Thomas
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[jon]

Ah, yes I had to make that "c.IdCage", thanks. 

var

 

ext = scope.Extent<Cage>();

 

 

var result = from c in ext

 

 

join a in scope.Extent<Animal>() on c.IdCage equals a.IdCage1 into cageanimals

 

 

from ca in cageanimals.DefaultIfEmpty()

 

 

select new

 

{ ... }

But now I get the vague error:  "Object not set to an instance of an object."  Would this happen if I do not have data in all of my "a.IdCage1" fields?

[Thomas]

Hello jon,

a stack trace could be really helpful now to analyze this further. Structurally I'm doing the same thing in my test and here we dont see such a behavior.

Regards,
Thomas
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[kunal]

Hi Friends

I also got same error "Object not set to an instance of an object." in left join query .. in this case my one table contain 4 to 5 records and other table is Empty.....

Query is as follows.........................

List<SubContentTypeandRevenuShareDatum> result = (from aSubContentType in objScope.Extent<SubContentType>()
join aRevenuShareDatum in objScope.Extent<RevenueShareDatum>() on aSubContentType.Id equals aRevenuShareDatum.ReferenceId into ps
from x in ps.DefaultIfEmpty()
select new SubContentTypeandRevenuShareDatum
{
ReferenceId = (x.ReferenceId == null ? aSubContentType.Id.ToString() : x.ReferenceId.ToString()),
AbsoluteValue = x.AbsoluteValue,
AgreementId = x.AgreementId,
AgreementType = x.AgreementType,
UpdatedOn = x.UpdatedOn
}).ToList<SubContentTypeandRevenuShareDatum>();

Please help me ............... Waiting For u'r replay............

[Thomas]

Hi kunal,

 I guess in your case the access to x.ReferenceId is failing; x might be null as DefaultIfEmpty is used.

Sincerely yours,
Thomas
the Telerik team