none
Cannot create a LINQ query for multiple tables in datacontext RRS feed

  • Question

  • I cannot find example to do this in datacontext. I need to return col's from multiple table query. I've tried SPROC and view, and creating partical public class. I can do this in codebehind which follows.  BUT I would really like to do with query in partial public class with LINQ or SPROC and the built in Update, Inquiry, Delete BUT I cannot find workable examples.  Following is codebehind:
    Protected Sub drpAvailLeases_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles drpAvailLeases.SelectedIndexChanged
    
            Dim db As New VOGASSQLDataContext
    
            Dim MyLease = From lse In db.Leases, nty In db.Entities, lsestg In db.LeaseStages, prdstg In db.ProductionStages _
    
                              Where lse.LeaseName = CType(drpAvailLeases.SelectedValue, String) _
    
                              Select lse.LeaseName, lse.Entity, nty.EntityName, lse.LeaseStage, lsestg.LeaseTitle, lse.ProductionStage, prdstg.StageTitle
    
            '2/26/10 returns selected Lease and Matching Entity name and leasestate title and production stage stage title 
    
    
    
            lsvLeaseBasic.DataSource = MyLease
    
            lsvLeaseBasic.DataBind()
    
        End Sub
    
    
    I have a long trail of what will not work which I haven't completely detailed here but can if u need it.  Again my objective is make use of the ORM and standaridized methods in Partial Public Class / SPROC.
    Thanks for any help,
    Pauley
    Monday, March 1, 2010 10:27 PM

Answers


  • As to your 2) "If #1 is viable, can you then work within the entities to project the data needed via a dynamic entity? "  I would love to place this query within the Partial Public Class somehow to bring down the data, so that I can reuse in as a query method. 

    This is confusing to me...what is stopping you and which partial class are you talking about? I do this all the time. (see Tribal Knowledge: Linq Entities To Do Your Bidding via the Partial Keyword in C# ) (Unless I am really not understanding you, to that end sorry) but simply place a static, (C# code sorry) on the target partial and return a valid class to be consumed.


    public static MyLeaseAndTitleInfo GetLeaseTitleInfo(DataContext dc ) // Allows for changes to data. If data won't be changed generate intenally.
    {
         return  from lse In dc.Leases,
     nty In db.Entities,
     lsestg In db.LeaseStages,
     prdstg In db.ProductionStages
     Where lse.LeaseName = CType(drpAvailLeases.SelectedValue, String)
        Select new MyLeaseAndTitleInfo
        {
                Name = lse.LeaseName,
                Entity = lse.Entity,
                EName = nty.EntityName,
                LeaseStage = lse.LeaseStage,
                Title = lsestg.LeaseTitle,
                ProdStage = lse.ProductionStage,
                StageTitle = prdstg.StageTitle
            };
    }


    William Wegerson (www.OmegaCoder.Com)
    • Marked as answer by pauley Saturday, March 6, 2010 6:41 PM
    Tuesday, March 2, 2010 10:24 PM
    Moderator

All replies

  • I cannot find example to do this in datacontext. I need to return col's from multiple table query.

    Sounds like a join, could you provide to us the tables in question instead of the failed code you tried? Thanks.


    William Wegerson (www.OmegaCoder.Com)
    Monday, March 1, 2010 10:46 PM
    Moderator
  • William thanks for responding.  Following is the latest Partial Public Class that I tried. This one has syntax problems but  I have tried many others, I just can't find all of them right now.  I have tried joins in the Partial Public Class, but I am clearly lacking in knowledge.  I do need an example of a Partial Public Class with A QUERY using vb.  I have searched for months to find query that will work. Updates, inserts, and etc are numerous. 

    Again, No problem with codebehind. The first post shows working codebehind and I have working joins in codebehind.  Following is attempt at Partial Class.
    Thanks for any help.

    NOTE: GetLease, which queries the one table works fine.  GetLeaseTwo, the 2nd, does have syntax problem but I've tried many others including joins. 

    Imports Microsoft.VisualBasic
    Imports System.Linq
    Imports System.Data.Linq
    Imports System.Collections.Generic
    Imports System.Data
    Imports System.Data.SqlClient.SqlDataReader
    
    '03/01/10 Imports System.Data.Linq.Table(Of Lease)
    Imports System
    
    Imports System.ComponentModel
    
    Imports System.Data.Linq.Mapping
    
    Imports System.Linq.Expressions
    Imports System.Reflection
    
    Partial Public Class VOGASSQLDataContext
        Public Function GetLease(ByVal lease As String) As Lease
            Return (From c In Me.Leases _
            Where c.LeaseName = lease _
                   Select c).Single
            'Select c.LeaseName, c.LeaseStage, c.Entity, c.Entity1).Single
        End Function
        Public Function GetLeasePlus(ByVal leaseName As String) As Lease
            Return (From lse In Me.Leases, nty In Me.Entities, psg In Me.ProductionStages, lsg In LeaseStages _
            Where lse.LeaseName = leaseName _
            Select lse, lsg, nty, psg).Single
        End Function
    
    
    
       
    
    End Class
    Tuesday, March 2, 2010 2:43 AM
  • William thanks for responding.  Following is the latest Partial Public Class that I tried.


    Ok I understand better now. The partial class (AFAIK) is only available after the entity is returned. The process of creating the SQL has to be directed at the server which does not know hide nor hair of the partial.

    With that said, let us break the problem down into two steps.

    1) Can you create a Linq query which brings down all the data? If not why can't a raw query get at the data?

    2) If #1 is viable, can you then work within the entities to project the data needed via a dynamic entity?

    HTH

     

     


    William Wegerson (www.OmegaCoder.Com)
    Tuesday, March 2, 2010 3:26 PM
    Moderator
  • Thanks William fo responding.  Yes I can create a query that brings down (returns) all the data.  The query is in codebehind which I show below from my 1st post:

    As to your 2) "If #1 is viable, can you then work within the entities to project the data needed via a dynamic entity? "  I would love to place this query within the Partial Public Class somehow to bring down the data, so that I can reuse in as a query method. 
    I cannot find an example to do this.  I find some that will bring down(return) from one table but NOT multiple tables(entity classes). I have an working example that will return all cols from one table. It apparently uses the predefined method GetTable which was automatically created by the o/rm designer.
    Pauley

    Protected Sub drpAvailLeases_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles drpAvailLeases.SelectedIndexChanged
    
            Dim db As New VOGASSQLDataContext
    
            Dim MyLease = From lse In db.Leases, nty In db.Entities, lsestg In db.LeaseStages, prdstg In db.ProductionStages _
    
                              Where lse.LeaseName = CType(drpAvailLeases.SelectedValue, String) _
    
                              Select lse.LeaseName, lse.Entity, nty.EntityName, lse.LeaseStage, lsestg.LeaseTitle, lse.ProductionStage, prdstg.StageTitle
    
            '2/26/10 returns selected Lease and Matching Entity name and leasestate title and production stage stage title 
    
    
    
            lsvLeaseBasic.DataSource = MyLease
    
            lsvLeaseBasic.DataBind()
    
        End Sub
    
    Tuesday, March 2, 2010 5:03 PM

  • As to your 2) "If #1 is viable, can you then work within the entities to project the data needed via a dynamic entity? "  I would love to place this query within the Partial Public Class somehow to bring down the data, so that I can reuse in as a query method. 

    This is confusing to me...what is stopping you and which partial class are you talking about? I do this all the time. (see Tribal Knowledge: Linq Entities To Do Your Bidding via the Partial Keyword in C# ) (Unless I am really not understanding you, to that end sorry) but simply place a static, (C# code sorry) on the target partial and return a valid class to be consumed.


    public static MyLeaseAndTitleInfo GetLeaseTitleInfo(DataContext dc ) // Allows for changes to data. If data won't be changed generate intenally.
    {
         return  from lse In dc.Leases,
     nty In db.Entities,
     lsestg In db.LeaseStages,
     prdstg In db.ProductionStages
     Where lse.LeaseName = CType(drpAvailLeases.SelectedValue, String)
        Select new MyLeaseAndTitleInfo
        {
                Name = lse.LeaseName,
                Entity = lse.Entity,
                EName = nty.EntityName,
                LeaseStage = lse.LeaseStage,
                Title = lsestg.LeaseTitle,
                ProdStage = lse.ProductionStage,
                StageTitle = prdstg.StageTitle
            };
    }


    William Wegerson (www.OmegaCoder.Com)
    • Marked as answer by pauley Saturday, March 6, 2010 6:41 PM
    Tuesday, March 2, 2010 10:24 PM
    Moderator