none
How can I convert a simple t-sql multi-table join into an equivilent LINQ to SQL statement. RRS feed

  • Question

  • I have a simple t-sql statement as follows:

    select a.id as CAID, c.NAME, c.M_ADDR1, c.M_CITY, c.M_ST, c.M_ZIP
    from cust_association a
    join customer c on c.ID = a.EMPR_ID
    where a.CUST_ID = @CUST_ID
    ORDER BY CAID

    I would like to create a LINQ to SQL statement, but can only get it to use one table:

    var db = new DataContext(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);

    var customerTable = db.GetTable<EmpLocation>();
    var customerAssociationsTable = db.GetTable<CustomerAssociations>();

    IQueryable<EmpLocation> locations =
        from a in customerAssociationsTable
        join c in customerTable on a.EmployerId equals c.Id
        orderby c.Name, c.State, c.City, c.Address1
        where a.CustomerId == customerId
        select c;

    I would like to include the CAID column from the CustomerAssociation table, but not sure how to do this.

     

    • Edited by Bill2010 Wednesday, October 20, 2010 2:04 PM
    Tuesday, October 19, 2010 9:46 PM

Answers

  • Hi Bill,

    I neglected that the EmpLocation is an entity type. Entities can be created outside of queries and inserted into the data store using a DataContext. You can then retrieve them using queries. However, you can't create entities as part of a query. For this scenario, you can create a new class my_EmpLocation which is the same as EmpLocation, but have a different name, then do something like:

    List<my_EmpLocation> locations =
      (from a in customerAssociationsTable
      join c in customerTable on a.EmployerId equals c.Id 
      orderby c.Name, c.State, c.City, c.Address1
      where a.CustomerId == customerId
      select new my_EmpLocation 
      {
        CAID = a.id, 
        NAME = c.NAME,
        M_ADDR1 = c.M_ADDR1, 
        M_CITY = c.M_CITY,
        M_ST = c.M_ST,
        M_ZIP = c.M_ZIP
     }).ToList();
    
    
    



    Then you can use this List<my_EmpLocation> locations to do what you want.  But if you still want to use the EmpLocation entity type, then you need to use the method you mentioned.

    public List<EmpLocation> GetEmployerLocations(string customerId)
    {
     var db = new DataContext(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
    
     var customerTable = db.GetTable<EmpLocation>();
     var customerAssociationsTable = db.GetTable<CustomerAssociations>();
    
     var query =
      from a in customerAssociationsTable
      join c in customerTable on a.EmployerId equals c.Id
      orderby c.Name, c.State, c.City, c.Address1
      where a.CustomerId == customerId
      select new { CAID = a.CAID, c };
    
     var locations = new List<EmpLocation>();
     
     foreach (var item in query)
     {
      item.c.CAID = (int)item.CAID;
      locations.Add(item.c);
     }
    
     return locations;
    }
    
    

     

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Bill2010 Friday, October 22, 2010 2:02 PM
    Friday, October 22, 2010 5:56 AM
    Moderator

All replies

  • Hi Bill2010,

    Would you like to try this:

    IQueryable<EmpLocation> locations =
        from a in customerAssociationsTable
        join c in customerTable on a.EmployerId equals c.Id
        orderby c.Name, c.State, c.City, c.Address1
        where a.CustomerId == customerId
        select new {CAID = a.id, c };

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, October 20, 2010 3:29 AM
    Moderator
  • This looks close. However, I am getting the following errors for:

    select new { c.CAID = a.CustomerAssociationId, c };

    select error: "Cannot implicitly convert type 'System.Linq.IQueryable<AnonymousType#2>' to 'System.Linq.IQueryable<CorVel.MobileApps.Domain.Entities.EmpLocation>'. An explicit conversion exists (are you missing a cast?)"

    c.CAID = a.CustomerAssociationId error: "Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access."

    IOW, I am attempting to add a new column "c.CAID" that does not exist in the db customer table, but needs to be returned in a Web Service call.

    Wednesday, October 20, 2010 2:24 PM
  • This link really helped me out...

     

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/29ba66ea-79b0-4ed0-be36-3479664009f1


    Warren C. LaFrance, Jr. Developer Hayes Software Systems, Inc. 11910 Anderson Mill Rd. Ste. 6 Austin, TX 78726-1137 wlafrance@hayessoft.com 512 – 219-7610 Business www.hayessoft.com
    Wednesday, October 20, 2010 2:52 PM
  • I made the following attempt to update the c.CAID column with a.CAID, but still getting compile errors:

    var locations =
        from a in customerAssociationsTable
        join c in customerTable on a.EmployerId equals c.Id
        orderby c.Name, c.State, c.City, c.Address1
        where a.CustomerId == customerId
        select new { c.CAID = a.CAID, c };

    New Compile Errors:

    • "An expression tree may not contain an assignment operator"
    • "Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access"
    Wednesday, October 20, 2010 3:50 PM
  • Ok, the following code works, but does not seem elegant to me:

    public List<EmpLocation> GetEmployerLocations(string customerId)
    {
      var db = new DataContext(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
    
      var customerTable = db.GetTable<EmpLocation>();
      var customerAssociationsTable = db.GetTable<CustomerAssociations>();
    
      var query =
        from a in customerAssociationsTable
        join c in customerTable on a.EmployerId equals c.Id
        orderby c.Name, c.State, c.City, c.Address1
        where a.CustomerId == customerId
        select new { CAID = a.CAID, c };
    
      var locations = new List<EmpLocation>();
     
      foreach (var item in query)
      {
        item.c.CAID = (int)item.CAID;
        locations.Add(item.c);
      }
    
      return locations;
    }
    
    Wednesday, October 20, 2010 4:04 PM
  • Hi Bill2010,

    I feel a little confused by what you are trying to do in your last post, it seems not compatible with the t-sql in your original post. The t-sql indicates that you are just selecting cust_association's id as the CAID, but in your last post, you assign the customerAssociationsTable's id to the customerTable's CAID, could you please clarify your real intention ?

    By the way, it's my mistake in my first post which cause the compile error. If you would like to include the CAID column from the CustomerAssociation table, then you need to use an anonymous type, but not EmpLocation as the query result, something like this:

    var locations =
        from a in customerAssociationsTable
        join c in customerTable on a.EmployerId equals c.Id
        orderby c.Name, c.State, c.City, c.Address1
        where a.CustomerId == customerId
        select new {CAID = a.id, c };

    However, if you want to return a List<EmpLocation>, and the Emplocation class also contains a CAID property, then you could do something like this:
      
    List<EmpLocation> locations =
        (from a in customerAssociationsTable
        join c in customerTable on a.EmployerId equals c.Id
        orderby c.Name, c.State, c.City, c.Address1
        where a.CustomerId == customerId
        select new EmpLocation 
        {
            CAID = a.id,
            NAME = c.NAME,
            M_ADDR1 = c.M_ADDR1, 
            M_CITY = c.M_CITY,
            M_ST = c.M_ST,
            M_ZIP = c.M_ZIP
      }).ToList();

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, October 21, 2010 3:19 AM
    Moderator
  • Sorry... I should have posted the entire method. The result set for the t-sql had a subsequent "foreach" loop that manually populated the List<EmpLocation? entity that is returned to the client via a REST WCF web service. As you can no doubt tell, I am new to LINQ, but the LINQ query to accomplish this was trivial when there was only a single table involved. It turned out that we also needed to include the CAID data from an associated table. I was hoping to build the employer location entity in a single query without the need to do an initial query and then an additional pass to actuall build the final table.

    Your suggestion above looks promising, so I will give it a try today.

    Thursday, October 21, 2010 2:17 PM
  • Alex,

    I tried implementing your code suggestion and am now getting the following error:

    "Explicit construction of entity type 'MyCompany.MyApp.Domain.Entities.EmpLocation' in query is not allowed."

    Any ideas on this?

    -- Bill

    Thursday, October 21, 2010 2:48 PM
  • Hi Bill,

    I neglected that the EmpLocation is an entity type. Entities can be created outside of queries and inserted into the data store using a DataContext. You can then retrieve them using queries. However, you can't create entities as part of a query. For this scenario, you can create a new class my_EmpLocation which is the same as EmpLocation, but have a different name, then do something like:

    List<my_EmpLocation> locations =
      (from a in customerAssociationsTable
      join c in customerTable on a.EmployerId equals c.Id 
      orderby c.Name, c.State, c.City, c.Address1
      where a.CustomerId == customerId
      select new my_EmpLocation 
      {
        CAID = a.id, 
        NAME = c.NAME,
        M_ADDR1 = c.M_ADDR1, 
        M_CITY = c.M_CITY,
        M_ST = c.M_ST,
        M_ZIP = c.M_ZIP
     }).ToList();
    
    
    



    Then you can use this List<my_EmpLocation> locations to do what you want.  But if you still want to use the EmpLocation entity type, then you need to use the method you mentioned.

    public List<EmpLocation> GetEmployerLocations(string customerId)
    {
     var db = new DataContext(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
    
     var customerTable = db.GetTable<EmpLocation>();
     var customerAssociationsTable = db.GetTable<CustomerAssociations>();
    
     var query =
      from a in customerAssociationsTable
      join c in customerTable on a.EmployerId equals c.Id
      orderby c.Name, c.State, c.City, c.Address1
      where a.CustomerId == customerId
      select new { CAID = a.CAID, c };
    
     var locations = new List<EmpLocation>();
     
     foreach (var item in query)
     {
      item.c.CAID = (int)item.CAID;
      locations.Add(item.c);
     }
    
     return locations;
    }
    
    

     

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Bill2010 Friday, October 22, 2010 2:02 PM
    Friday, October 22, 2010 5:56 AM
    Moderator
  • Thanks for the heads up, Alex. I renamed EmpLocation to Customer, removed the CAID field, and created a new EmpLocation class that contains data from both cust_association and customer. It is much cleaner now.
    Friday, October 22, 2010 2:25 PM