Left Join

19 posts, 1 answers
  1. Alfred Ortega
    Alfred Ortega avatar
    193 posts
    Member since:
    May 2005

    Posted 06 Apr 2009 Link to this post

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

    Thanks,
    Al
  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 07 Apr 2009 Link to this post

    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.
  3. DevCraft banner
  4. Alfred Ortega
    Alfred Ortega avatar
    193 posts
    Member since:
    May 2005

    Posted 07 Apr 2009 Link to this post

    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 

  5. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 07 Apr 2009 Link to this post

    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.
  6. Alfred Ortega
    Alfred Ortega avatar
    193 posts
    Member since:
    May 2005

    Posted 07 Apr 2009 Link to this post

    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
  7. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 08 Apr 2009 Link to this post

    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.
  8. Alfred Ortega
    Alfred Ortega avatar
    193 posts
    Member since:
    May 2005

    Posted 08 Apr 2009 Link to this post

    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
  9. Answer
    Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 09 Apr 2009 Link to this post

    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.
  10. Alfred Ortega
    Alfred Ortega avatar
    193 posts
    Member since:
    May 2005

    Posted 09 Apr 2009 Link to this post

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

    Al
  11. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 09 Apr 2009 Link to this post

    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.
  12. Alfred Ortega
    Alfred Ortega avatar
    193 posts
    Member since:
    May 2005

    Posted 09 Apr 2009 Link to this post

    Would that work with if it started:

    var ext = scope.Extent<Guy>().Skip(10).Take(10);
  13. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 14 Apr 2009 Link to this post

    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.
  14. Alfred Ortega
    Alfred Ortega avatar
    193 posts
    Member since:
    May 2005

    Posted 14 Apr 2009 Link to this post

    Thanks for the update and I'll keep that in mind.  I do hope that maybe Q2 or Q3 will have this functionality though.
  15. jon
    jon avatar
    33 posts
    Member since:
    Dec 2008

    Posted 01 Jul 2009 Link to this post

    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?
  16. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 02 Jul 2009 Link to this post

    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.
  17. jon
    jon avatar
    33 posts
    Member since:
    Dec 2008

    Posted 02 Jul 2009 Link to this post

    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?

  18. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 06 Jul 2009 Link to this post

    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.
  19. kunal
    kunal avatar
    11 posts
    Member since:
    Jan 2009

    Posted 14 Sep 2010 Link to this post

    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............
  20. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 16 Sep 2010 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
Back to Top
DevCraft banner