none
VB.Net SQL query to LINQ equivalent Group BY OUTER JOIN multiple tables

    Question

  • I tried to figure out but could not make it. Can anybody help in creating the VB.Net LINQ equivalent of this query. It uses fields from both tables, that is from the left table and group table along with group vaue. All examples show fields from left table and only the aggregate/group value from the right group table. Kindly translate this query into VB.Net LINQ syntax

    Select a.Field1, a.Field2, b.Field1, Count(b.Field2) as tCount,
    a.Field3 from Table1 a 
    left outer join Table2 b
    on a.Field1=b.Field2
    where  b.Field1=2 or b.Field1 is  null
    Group By a.Field1, a.Field2, b.Field1, a.Field3

    Many Thanks

    Thursday, October 31, 2013 1:25 PM

All replies

  • Hello,

    Have a try with codes below:

    Dim db As DataClasses1DataContext = New DataClasses1DataContext()
    
            Dim result = (From a In db.as
    
                        Group Join b In db.bs On a.Field2 Equals b.Field2 Into Group
    
                        From g In Group.DefaultIfEmpty
    
                        Select a.Field1, a.Field2, a.Field3, BF1 = g.Field1, BF2 = g.Field2
    
                        Group BF2 By Field1, Field2, Field3, BF1 Into Group
    
                        Select Field1, Field2, Field3, BF1, Group.Count
    
                        Where BF1 = 2
    
                        ).ToList()
    

    It is not completely same with yours, however, you can modify it to reach your requirements.

    And there are samples for Groug by and Count:

    http://msdn.microsoft.com/en-us/library/bb918069.aspx

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 01, 2013 4:28 AM
  • Thanks a lot. Your reply is spot on and works perfectly okay except one missed item. One thing that you missed to mention unfortunately stuck me again though. That is an additional condition in the where clause for a null value. BF1 is a Decimal value.

    or BF1 Is Null

    I tried several solutions that I could find in this forum and several others online but none worked for me. For example

    OR BF1 IS Nothing

    OR BF1 IS Null

    OR IsNothing(BF1)

    OR ISDBNull(BF1)

    OR BF1 = Null

    OR BF1 = System.DBNull

    nothing seemed to work unfortunately :(. Without this small bit I can not get the actual results I am looking for because this will help me get the full list of records in the left table (left outer join) and the counts for the right table wherever exists. 

    Please suggest!

    Regards,

    Friday, November 01, 2013 3:52 PM
  • It is strange that I try with codes below:

    Dim result = (From a In db.as Group Join b In db.bs On a.Field2 Equals b.Field2 Into Group From g In Group.DefaultIfEmpty Select a.Field1, a.Field2, a.Field3, BF1 = g.Field1, BF2 = g.Field2

    Group BF2 By Field1, Field2, Field3, BF1, ID Into Group Select Field1, Field2, Field3, BF1, Group.Count

    Where BF1 = 2 Or BF1 Is Nothing ).ToList()

    It works fine.

    It actually is same with your first try.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 04, 2013 9:17 AM
  • Fred,

    Thanks again for the reply. BF1 is a double value. When I try BF1 Is Nothing I get the following error message

    "Cannot execute text selection: 'Is' operator does not accept operands of type 'Decimal'. Operands must be reference or nullable types."

    When I try BF1.ToString Is Nothing, the following error messsage is produced

    "The null value cannot be assigned to a member with type System.Decimal which is a non-nullable value type."

    This way various combinations have failed. Kindly advise.

    Regards

    Monday, November 04, 2013 1:55 PM
  • It is appreciate that if you can share your table structure.

    Thanks.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 05, 2013 8:04 AM
  • Here is the tables schema. Database is SQL compact.

    CREATE TABLE [Table1] (
      [CID] numeric(18,0) NOT NULL
    , [Field1] numeric(18,0) NOT NULL
    , [Field2] nvarchar(4000) NULL
    , [Field3] nvarchar(4000) NULL
    );

    GO

     CREATE TABLE [Table2] (
      [CID] numeric(18,0) NOT NULL
    , [Field1] numeric(18,0) NOT NULL
    , [Field3] int NOT NULL
    , [Field4] nvarchar(4000) NULL
    , [Field2] numeric(18,0) NULL
    );

    In table1, CID, Field1 make a composite primary key. In Table2, CID, Field1, Field3 make a composite primary key. Field2 is a foreign key for Field1 of Table1. CID in composite keys also acts as a foreign key to a main table not included here.

    Field1 in table2 also acts as foreign key to one other table but no data is fetched from there so the query has no reference to that. 

    After having these table, if you read the query, hopefully it will make more sense.

    ---

    As of my try, I found out that when I remove the primary key from table 2, the query runs successfully with "IS Nothing" statement. The reason is that after removing the primary key, the table allows entering null values of the key (including Field1 that is part of composite key), so LINQ assumes Field1 (BF1 in linq) of table2 to be nullable hence no error on "IS" operand. If it is part of the key, LINQ assumes that it can not be null at any time hence non-nullable so the "IS" keyword is rejected.

    But in scenario of the query, since table1 is the left side table of the left outer join, it has to show all records, while only BF1 and corresponding Counts of group from the right table (table2) are included in the result set. In that scenario, where no corresponding records for the join are found in table2, the column Field1/BF1 of table2 will be returned as NULL and the group Count value as 0. 

    It is where the SQL query returns the correct result with the statement "b.Field1 Is Null" while LINQ rejects the equivalent "BF1 IS Nothing" assuming that it can not be null and the operand has to be a nullable value/column.

    Am I making sense? :) Sorry to trouble you on that so much. Thanks again for efforts and kind suggestions.

    Many Regards

    Wednesday, November 06, 2013 2:22 AM
  • >>"Cannot execute text selection: 'Is' operator does not accept operands of type 'Decimal'. Operands must be reference or nullable types."

    The BF1 is a double type value, however, it does not belong to nullable types. We can not set a double(Or Int...) type to null like:

      Dim a As Integer
            a = Nothing

    Double type is value types which is not inherited object. Only type inherited Object can be set to null.

    >>"The null value cannot be assigned to a member with type System.Decimal which is a non-nullable value type."

    This is because LINQ to Sql does not support custom method.

    Change the Field1 in table2 to string type then it will be ok.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 06, 2013 8:36 AM
  • Hi Fred,

    Thanks again. I understand your explanations and changing the datatype of BF1 to string in database can get the query working but unfortunately I can't change the data type, as said, it is part of a key and also acts as a foreign key to another table. Entity Framework model will also show error on such a change.

    I could extract the data directly from database through query but I also need to query the unsaved records. While I can query the EF Context to retrieve the unsaved records for all other needs except for this particular LINQ to SQL.

    Could you suggest a way where I can use the SQL syntax to query the current EF context which has the unsaved data in it so that the results will include the counts of unsaved records as well? Or perhaps some other approach to retrieve data from context avoiding LINQ to SQL?

    Regards 

    Wednesday, November 06, 2013 11:28 AM
  • I have a idea that we can use the procedure and the 'case when' in sqlserver.

    Since the b.Filed1 is a value type column, we can not set it to be null in program. However, we can sign the null to be specify number as like -1.

    Procedure Codes like below:

    CREATE PROCEDURE [dbo].[Procedure]
    	@param1 int = 0
    AS
    Select a.Field1, a.Field2,a.Field3, case when b.Field1 is null then -1 else b.Field1 end as BF1, Count(b.Field2) as tCount
     from  a 
    left outer join  b
    on a.Field1=b.Field2
    where  b.Field1= @param1 or b.Field1 is  null
    Group By a.Field1, a.Field2, b.Field1, a.Field3
    RETURN 0

    Call it in program:

    Dim result = db.Procedure(2).ToList()

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, November 07, 2013 9:19 AM
  • Fred I greatly appreciate your idea. This definitely would have worked but unfortunately the database is SQL Compact (4.0), so no luxury of procedures :)

    I was just thinking of an Odd way if you could help me get the context right for that in LINQ to SQL. I know it is creepy but might at-least get things going. The idea is,

    1.  We use the LINQ to SQL query as you suggested without the BF1 IS Nothing condition to get the list of available counts for BF1=2.
    2. Another LINQ to SQL to get the list of a.field1, a.field2, a.field3, 0, 0 from A (Table1) minus all the a.field1 that are found in the first query. To understand the scenario, the SQL query syntax where clause might look something like this (where a.field1 not in (select a.field1 from first query)) so you can get the idea what i mean
    3. Join the result of 1 and 2 order by Field1.

    All this in LINQ to SQL. Possible? :)

    Many Thanks

    Thursday, November 07, 2013 4:16 PM
  • OK, the DB is  SQL Compact.

    For the idea, I do not recommend it because it will create a redundant sql command.

    How about query the database to sql command directly.

    For this, we need to define a custme class like below:

    Public Class View
        Private _Field1 As Integer
    
        Private _Field2 As Integer
    
        Private _Field3 As Integer
    
        Private _BF1 As System.Nullable(Of Integer)
    
        Private _tCount As System.Nullable(Of Integer)
    
        Public Property Field1() As Integer
            Get
                Return Me._Field1
            End Get
            Set(value As Integer)
                If ((Me._Field1 = value) _
                            = False) Then
                    Me._Field1 = value
                End If
            End Set
        End Property
    
        Public Property Field2() As Integer
            Get
                Return Me._Field2
            End Get
            Set(value As Integer)
                If ((Me._Field2 = value) _
                            = False) Then
                    Me._Field2 = value
                End If
            End Set
        End Property
    
        Public Property Field3() As Integer
            Get
                Return Me._Field3
            End Get
            Set(value As Integer)
                If ((Me._Field3 = value) _
                            = False) Then
                    Me._Field3 = value
                End If
            End Set
        End Property
    
        Public Property BF1() As System.Nullable(Of Integer)
            Get
                Return Me._BF1
            End Get
            Set(value As System.Nullable(Of Integer))
                If (Me._BF1.Equals(value) = False) Then
                    Me._BF1 = value
                End If
            End Set
        End Property
    
        Public Property tCount() As System.Nullable(Of Integer)
            Get
                Return Me._tCount
            End Get
            Set(value As System.Nullable(Of Integer))
                If (Me._tCount.Equals(value) = False) Then
                    Me._tCount = value
                End If
            End Set
        End Property
    End Class
    

    And then use this type in method like below:

    Dim queryString As String = String.Format("Select a.Field1, a.Field2,a.Field3, b.Field1 BF1, Count(b.Field2) as tCount from a left outer join  b on a.Field1=b.Field2 where b.Field1 = {0} Or b.Field1 Is null Group By a.Field1, a.Field2, b.Field1, a.Field3", 2)
    
    Dim results As IEnumerable(Of View) = db.ExecuteQuery(Of View)(queryString).ToList()
    It will return a collection which type is View.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 08, 2013 9:04 AM
  • Thanks Fred. I guess this solution will read directly from database and not from Context? I also need the unsaved records to be returned, in this case, those records will be missed, isn't?

    Example 

    Context has two records to return and record#2 has not been saved yet. Database has 1 record in it. So the query that runs directly will return the saved record only?

    Regards,

    Friday, November 08, 2013 3:25 PM
  • >> I guess this solution will read directly from database and not from Context?

    Being strange that why you want to query data form context?

    Because the data from context comes from the database, too.

    Thursday, November 21, 2013 9:58 AM