Left Outer Join Using Linq

[Muhammad]

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

[Alexander]

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

[Muhammad]

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()

[Alexander]

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

[Garrett]

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

[Alexander]

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