none
LINQ join on different types (int, string) RRS feed

  • Question

  • I have two tables, something like this:

    [Table1]
    Field1 (int)

    [Table2]
    Field2 (nvarchar[n])

    I want to perform a join on these tables where the Field1 (converted to a string) is the same as Field2. I know this isn't a "nice" structure combining on different types, but the circumstances require this.

    How can I do this? I tried simply converting Field1 to a string, but it fails when executing the query (telling it cannot convert between int and string):

    var Results = from T1 in DataContext.Table1
    join T2 in DataContext.Table2 on new
    {
      F = T1.Field1.ToString(),
    }
    equals new
    {
      F = T2.Field2,
    }
    select ...
    Monday, June 29, 2009 12:26 PM

Answers

  • Joining on anonymous types is only necessary if more than one field is involved so you can simplify it to:

    from t1 in datacontext.table1
    join t2 in datacontext.table2 on t1.field1.ToString() equals t2.field2
    select ...


    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by Magos Monday, June 29, 2009 2:21 PM
    Monday, June 29, 2009 12:53 PM
    Answerer
  • I have tried and it works fine for me, in LINQPad I have

    from t1 in Table1s
    join t2 in Table2s
    on t1.Field1.ToString() equals t2.Field2
    select t1.Foo
    and the corresponding SQL is

    SELECT [t0].[Foo]
    FROM [Table1] AS [t0]
    INNER JOIN [Table2] AS [t1] ON (CONVERT(NVarChar,[t0].[Field1])) = [t1].[Field2]
    



    MVP XML My blog
    • Marked as answer by Magos Monday, June 29, 2009 2:21 PM
    Monday, June 29, 2009 1:16 PM
  • No, it doesn't fail.

    A quick mockup using adventureworks of a one-field and two field join:

    var tq = from em in dc.Employees
             join a in dc.Addresses on em.ManagerID.ToString() equals a.AddressLine1
             select new { em, a };
    var t = tq.ToList();
    
    
    

    ...will hit the db with:

    exec sp_executesql N'SELECT [t0].[EmployeeID], [t0].[NationalIDNumber], [t0].[ContactID], [t0].[LoginID], [t0].[ManagerID], [t0].[Title], [t0].[BirthDate], [t0].[MaritalStatus], [t0].[Gender], [t0].[HireDate], [t0].[SalariedFlag], [t0].[VacationHours], [t0].[SickLeaveHours], [t0].[CurrentFlag], [t0].[rowguid], [t0].[ModifiedDate], [t1].[AddressID], [t1].[AddressLine1], [t1].[AddressLine2], [t1].[City], [t1].[StateProvinceID], [t1].[PostalCode], [t1].[rowguid] AS [rowguid2], [t1].[ModifiedDate] AS [ModifiedDate2]
    FROM [HumanResources].[Employee] AS [t0]
    INNER JOIN [Person].[Address] AS [t1] ON (CONVERT(NVarChar,[t0].[ManagerID])) = [t1].[AddressLine1]', N'';

    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by Magos Monday, June 29, 2009 2:21 PM
    Monday, June 29, 2009 1:21 PM
    Answerer

All replies

  • Joining on anonymous types is only necessary if more than one field is involved so you can simplify it to:

    from t1 in datacontext.table1
    join t2 in datacontext.table2 on t1.field1.ToString() equals t2.field2
    select ...


    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by Magos Monday, June 29, 2009 2:21 PM
    Monday, June 29, 2009 12:53 PM
    Answerer
  • I am in fact joining on two fields (thus why I use anonymous type), but simplified the example to only show the part I have problems with - join between different types.
    Have you actually tried your code? I'm sure it will fail for you.
    Monday, June 29, 2009 12:59 PM
  • I have tried and it works fine for me, in LINQPad I have

    from t1 in Table1s
    join t2 in Table2s
    on t1.Field1.ToString() equals t2.Field2
    select t1.Foo
    and the corresponding SQL is

    SELECT [t0].[Foo]
    FROM [Table1] AS [t0]
    INNER JOIN [Table2] AS [t1] ON (CONVERT(NVarChar,[t0].[Field1])) = [t1].[Field2]
    



    MVP XML My blog
    • Marked as answer by Magos Monday, June 29, 2009 2:21 PM
    Monday, June 29, 2009 1:16 PM
  • No, it doesn't fail.

    A quick mockup using adventureworks of a one-field and two field join:

    var tq = from em in dc.Employees
             join a in dc.Addresses on em.ManagerID.ToString() equals a.AddressLine1
             select new { em, a };
    var t = tq.ToList();
    
    
    

    ...will hit the db with:

    exec sp_executesql N'SELECT [t0].[EmployeeID], [t0].[NationalIDNumber], [t0].[ContactID], [t0].[LoginID], [t0].[ManagerID], [t0].[Title], [t0].[BirthDate], [t0].[MaritalStatus], [t0].[Gender], [t0].[HireDate], [t0].[SalariedFlag], [t0].[VacationHours], [t0].[SickLeaveHours], [t0].[CurrentFlag], [t0].[rowguid], [t0].[ModifiedDate], [t1].[AddressID], [t1].[AddressLine1], [t1].[AddressLine2], [t1].[City], [t1].[StateProvinceID], [t1].[PostalCode], [t1].[rowguid] AS [rowguid2], [t1].[ModifiedDate] AS [ModifiedDate2]
    FROM [HumanResources].[Employee] AS [t0]
    INNER JOIN [Person].[Address] AS [t1] ON (CONVERT(NVarChar,[t0].[ManagerID])) = [t1].[AddressLine1]', N'';

    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by Magos Monday, June 29, 2009 2:21 PM
    Monday, June 29, 2009 1:21 PM
    Answerer
  • This is mighty odd.
    I downloaded and tried LINQPad and I can run the same LINQ query fine (I get what seems like correct results). However if I run it from code (C#, VS 2008) I get the error.

    This is the query run in C# (fails):

    var Accounts = (from Account in DataContext.Accounts
     join AccountPlan in DataContext.AccountPlans on new
     {
    	 CompanyId = Account.tfFtgID,
    	 AccountNr = Account.tfKontoNr,
     }
     equals new
     {
    	 CompanyId = AccountPlan.tfFtgID,
    	 AccountNr = AccountPlan.tfKontoNr.ToString(),
     }
     where (Account.tfFtgID == Operator.CompanyId)
     orderby AccountPlan.tfKontoNr
     select new CAccount(Account.tfKontoNr ?? "", AccountPlan.tfBeskrivning ?? "")).Take(10);


    And this is its generated sql:

    SELECT TOP (10) [t2].[value] AS [Nr], [t2].[value2] AS [Name]
    
    FROM (
    
        SELECT COALESCE([t0].[tfKontoNr],@p0) AS [value], COALESCE([t1].[tfBeskrivning],@p1) AS [value2], [t0].[tfFtgID], [t1].[tfKontoNr]
    
        FROM [dbo].[regKontoNr] AS [t0]
    
        INNER JOIN [dbo].[regKontoplan] AS [t1] ON ([t0].[tfFtgID] = [t1].[tfFtgID]) AND ([t0].[tfKontoNr] = (CONVERT(NVarChar,[t1].[tfKontoNr])))
    
        ) AS [t2]
    
    WHERE [t2].[tfFtgID] = @p2
    
    ORDER BY [t2].[tfKontoNr]
    
    -- @p0: Input NVarChar (Size = 0; Prec = 0; Scale = 0) []
    
    -- @p1: Input NVarChar (Size = 0; Prec = 0; Scale = 0) []
    
    -- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
    
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1


    And this is the error I get:
    "Det gick inte att omvandla ett objekt av typen System.Int32 till typen System.String."
    Swedish, roughly translated:
    "Unable to convert and object of type System.Object to type System.String"

    This is the query run in LINQPad (succeeds):

    (from Account in RegKontoNrs
    join AccountPlan in RegKontoplans on new
    {
    	CompanyId = Account.TfFtgID,
    	AccountNr = Account.TfKontoNr,
    }
    equals new
    {
    	CompanyId = AccountPlan.TfFtgID,
    	AccountNr = AccountPlan.TfKontoNr.ToString(),
    }
    where (Account.TfFtgID == 1)
    orderby AccountPlan.TfKontoNr
    select new { KNr = Account.TfKontoNr ?? "", N = AccountPlan.TfBeskrivning ?? ""}).Take(10)
    


    Any ideas? Either a bug, or something so simple I plain fail to see it :)
    Monday, June 29, 2009 2:00 PM
  • Ok, I knew it was some stupid ____. Apparently I was working against an older database which had some differences in table layout.
    It works now, thanks for help :).
    Monday, June 29, 2009 2:20 PM

  • "Det gick inte att omvandla ett objekt av typen System.Int32 till typen System.String."

    There's a Swedish version of the .net framework?!? What will they come up with next... :)
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    Tuesday, June 30, 2009 3:36 AM
    Answerer
  • Type of Join LINQ to SQL
     

    var joins = from a in db.Authors join b in db.Books on a.AurthorId equals b.AurthorIdRef select new { a.AurthorName, b.BookName, b.Edition, b.Price }; var joinData = (from a in db.Authors join b in db.Books on a.AurthorId equals b.AurthorIdRef select new { a.AurthorName, b.BookName, b.Edition, b.Price }).ToList(); var joinTable = from a in db.Authors join b in db.Books on a.AurthorId equals b.AurthorIdRef where b.BookId == new Guid() select new { a.AurthorName, b.BookName, b.Edition, b.Price }; var joinWhere = from a in db.Authors join b in db.Books on a.AurthorId equals b.AurthorIdRef where b.BookId == new Guid() && b.BookName == "Asp.net" select new { a.AurthorName, b.BookName, b.Edition, b.Price }; var joinorderby = from a in db.Authors join b in db.Books on a.AurthorId equals b.AurthorIdRef where b.BookId == new Guid() orderby b.BookName descending select new { a.AurthorName,b.BookName }; var grupby = from b in db.Books group b by b.BookName into bookGroup where bookGroup.Count() > 0 orderby bookGroup.Key select bookGroup;



    Kamal Saxena iBOOK dESK (UITS INDIA)

    Wednesday, May 23, 2012 3:25 AM