Left Outer Join Using Linq

6 posts, 0 answers
  1. Muhammad
    Muhammad avatar
    87 posts
    Member since:
    Oct 2012

    Posted 04 May 2011 Link to this post

    Hi,

    I am trying to use linq to make a left outer join. I have two tables one is called FF_Item and the other is called FF_QuotationDetails. I want that the data from table FF_Items appears either there is a matching record exist in FF_QuotationDetails or not. But right now I am not getting any data in my gridview.

    Protected Sub rgGroupItem_DetailTableDataBind(ByVal source As Object, ByVal e As GridDetailTableDataBindEventArgs) Handles rgGroupItem.DetailTableDataBind


            Dim dataItem As GridDataItem = e.DetailTableView.ParentItem


            Dim ItemID As String = dataItem.GetDataKeyValue("ID").ToString()

            Dim items = From item In dbContext.FF_Items.DefaultIfEmpty
                        Join quotation In dbContext.FF_QuotationDetails
                        On item.ID Equals quotation.ItemID
                        Where (item.GroupID = ItemID)
                        Select item.ID, item.ItemName, item.Price, item.Rate, quotation.Quantity, quotation.TotalCost


            e.DetailTableView.DataSource = items.ToList  


        End Sub
  2. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 09 May 2011 Link to this post

    Hello Muhammad,

    You should be able to simplify the query a bit by starting from the FF_QuotationDetails endpoint and accessing the FF_Item referenced by each FF_QuotationDetail. The query would look like this:
    Dim items = (From quotation In dbContext.FF_QuotationDetails _
            Where quotation.FF_Item.GroupID = ItemID _
            Select quotation.FF_Item.ID, quotation.FF_Item.ItemName, quotation.FF_Item.Price, quotation.FF_Item.Rate, quotation.Quantity, quotation.TotalCost).ToList()

    I hope that helps.

    Kind regards,
    Alexander
    the Telerik team
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
  3. DevCraft banner
  4. Muhammad
    Muhammad avatar
    87 posts
    Member since:
    Oct 2012

    Posted 13 May 2011 Link to this post

    FF_Item is a different table. You can't make a where caluse like this because I am getting a compile time error. FF_QuotationDetails and FF_Item are two different tables.

    Dim items = (From quotation In dbContext.FF_QuotationDetails _
            Where quotation.FF_Item.GroupID = ItemID _
            Select quotation.FF_Item.ID, quotation.FF_Item.ItemName, quotation.FF_Item.Price, quotation.FF_Item.Rate, quotation.Quantity, quotation.TotalCost).ToList()
  5. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 17 May 2011 Link to this post

    Hello Muhammad,

    Yes, they are different tables but if you have an association between them (quotation.ItemID is the foreign key and item.ID is the primary key), you will get a navigation property generated (quotation.FF_Item) which will allow you to access the related FF_Item object in the linq query. I assumed that the association has been automatically generated but maybe it has not and that is why you are getting compilation errors. In this case you can define the association manually and see if the linq query works. Here is an article which describes how an association can be created.

    All the best,
    Alexander
    the Telerik team
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
  6. Garrett
    Garrett avatar
    23 posts
    Member since:
    Sep 2012

    Posted 06 Jun 2011 Link to this post

    Hello,

    I have the same problem as Muhammed.

    I've reviewed the link you provided, which I'm sure is right, however I do not have the ability to update the keys in the database. It's a replicated database that I have read-only access to.

    Is there a way around this? The associations are in the RLINQ file, but they do not work in code.

    Thanks,

    Garrett
  7. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 08 Jun 2011 Link to this post

    Hi Garrett,

    Could you please give us some more details about your setup? The table definitions as well as the query would like to perform and the result you expect should be enough for us to provide you with a solution or at least get a clear view on the situation.

    Greetings,
    Alexander
    the Telerik team
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
Back to Top
DevCraft banner