none
joining a list of Integers to an integer RRS feed

  • Question

  • Hi

    The problem is that I have objectA, which is a list of objectB. Now objectB has a property called DataId, which is an integer. Then I have objectC, which is a list of objectD. And objectD has a property called CompanyIds, which is a list of integers.

    What I need is to join objectA to objectC, using the following:

     

    From
     obA In
     objectA Join
     obC In
     objectC On
     obA.DataId Equals
     obC.CompanyIds _
    Select New objectB With {.DataId = DataId}).ToList()

     

    The problem is that  obA.DataId Equals obC.CompanyIds  is not allowed because 'Equals' cannot compare a value of 'Integer' with a value of 'System.Collections.Generic.List(Of Integer)'.

    I have just started with LINQ, so apologies if this is actually a simple question, but I can't figure out how to join this.

    I'm not sure if I need to use something called a Lambda expression to achieve this...

    Thanks

    p.s. I'm using vb.net

    Wednesday, April 28, 2010 10:26 AM

All replies

  • Hi Enocht,

    When comparing a single item to a list of items I would use the Contains method.  I would suggest the following alteration.

     

    From
     obA In
     objectA Join
     obC In
     objectC On
     obC.CompanyIds.Contains(obA.DataId) _
    
    Select
     New
     objectB With
     {.DataId = DataId}).ToList()
    

     

    The Contains method is used for determining whether an element is within a list.

    I hope this helps.  Please let me know if there are any questions or other issues (I haven't had chance to test this yet but the theory's sound ^^).

    Rob

    Wednesday, April 28, 2010 6:03 PM
  • Hello,

     

    Welcome to LINQ to SQL forum!

     

    I agree with Rob that we can use the .Contains method in LINQ to SQL, and it will be translated into a “WHERE IN” SQL statement, http://blog.wekeroad.com/blog/creating-in-queries-with-linq-to-sql/.  

     

    If it does not solve the problem, please provide us with more detailed information about your scenario and database structure, we are willing to help. 

     

    Have a nice day, both!

     

     

    Best Regards,
    Lingzhi Sun

    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, April 29, 2010 6:46 AM
    Moderator
  • Hi Rob,

    Thanks for the suggestion. I tried your method but, it complains that you need an Equals operator.

    But I have found another way to get around this problem now.

    Thanks for your help

    Thursday, April 29, 2010 12:19 PM
  • Hi Enocht,

    I'm glad you managed to solve the problem.  Perhaps you could share the answer as it may be useful for anyone who searches for an answer to a similar issue in future?

    Thanks,

    Rob

    Friday, April 30, 2010 1:25 PM
  • It was kind of a work around. But, basically I had to create a loop to give me the IDs I wanted. Not elegant, but it worked:

    Dim listOfIds As New List(Of Integer)
    
    For Each obD As objectD In obC
    	If obD.CompanyId IsNot Nothing AndAlso obD.CompanyId.Count > 0 Then
    		For Each ID As Integer In obD.CompanyId
    			listOfIds.Add(ID)
    		Next
    	End If
    Next
    
    
    Return (From obA In objectA Join res In listOfIds On obA.DataId Equals res _
    Select New objectB With {.DataId = DataId}).ToList()

    Friday, April 30, 2010 5:14 PM
  • It was kind of a work around. But, basically I had to create a loop to give me the IDs I wanted. Not elegant, but it worked:

     

    Dim
     listOfIds As
     New
     List(Of Integer
    )
    
    For
     Each
     obD As
     objectD In
     obC
    	If
     obD.CompanyId IsNot
     Nothing
     AndAlso
     obD.CompanyId.Count
     > 0 Then
    
    		For
     Each
     ID As
     Integer
     In
     obD.CompanyId
    			listOfIds.Add(ID)
    		Next
    
    	End
     If
    
    Next
    
    
    
    Return
     (From
     obA In
     objectA Join
     res In
     listOfIds On
     obA.DataId Equals
     res _
    Select
     New
     objectB With
     {.DataId = DataId}).ToList()
    

     


    Hi enocht

    Yes you managed to do it the old way, but you asked a question in a Linq To Sql forum, because you wanted to learn something about Linq, right ;-)

    The last for each you wrote can easily be written like:

    var listOfids = from obD in objectD select new{objD.CompanyId}; // spice it up with a where if needed.

     

    That was the first thing... but not exactly what your first question was targeted at :)

    Please take a look here:

    Using your list of ObjectA's which has a property of DataId, place the id's in a variable called listofIds

    var listofIds = objectA.Select(x => x.DataId).ToList<int>();

    Then as I read your objects you also have an ObjectD, but with a property consisting of a list of companyid's , right? If that is the case then:


                var s = from id in ObjectD.CompanyId.Intersect(listofIds)
                        select new
                        {
                            DataId = id
                        };

     

    As you see, you can just use Intersect instead of joining when it comes to a single property. In the above linq I'm just selecting all the id's out into a anonymous object, whereas you are selecting them into objectB

    I hope it cleared things a bit up :)

    Friday, April 30, 2010 10:30 PM
  • Hi

     

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    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, May 5, 2010 1:59 AM
    Moderator
  • Hi Janus007,

    Ideally, what I need is to be able to select the list of CompanyIds. I have an object (result), which is a list of ItemX and ItemX has a property (CompanyIds), which is a list of Integers.

    So I need to obtain that list of Integers to do that Intersect. I thought this might work:

    Dim listOfCompanyIds As List(Of Integer) = result.Select(Function(x) x.CompanyId.Select(Function(y) y.Value).ToList).ToList

    ... but it gives me the error:

    Value of type 'System.Collections.Generic.List(Of System.Collections.Generic.List(Of Integer))' cannot be converted to 'System.Collections.Generic.List(Of Integer)'.

     

    Thanks for your help

    Thursday, May 6, 2010 2:46 PM
  • Hi Janus007,

    Ideally, what I need is to be able to select the list of CompanyIds. I have an object (result), which is a list of ItemX and ItemX has a property (CompanyIds), which is a list of Integers.

    So I need to obtain that list of Integers to do that Intersect. I thought this might work:

     

    Dim
     listOfCompanyIds As
     List(Of Integer
    ) = result.Select
    (Function
    (x) x.CompanyId.Select
    (Function
    (y) y.Value).ToList).ToList
    

     

    ... but it gives me the error:

    Value of type 'System.Collections.Generic.List(Of System.Collections.Generic.List(Of Integer))' cannot be converted to 'System.Collections.Generic.List(Of Integer)'.

     

    Thanks for your help


    But that looks like your initial post, please use my proposal with explicit declaration of the integer list :)
    Thursday, May 6, 2010 5:46 PM
  • Hi Janus007,

    You might have misunderstood, or I wasn't being too clear in my first post.

    If I have a list of objects called result and each item in this list has a property called CompanyIds (which is a list of Integers), then I'm not sure I can run your suggestion of:

    var listOfids = from res in result select new{res.CompanyIds};
    I guess the problem is that I need to drill one list further down to get at the CompanyIds, but I wasn't sure how that is possible using linq.

    To be clear, I have the following situation:

    Dim result As List(Of GlobalSearchItem)

    ...and a GlobalSearchItem contains the property CompanyIds (which is a list of integers).

    p.s. I use vb.net, sorry if I'm confusing things.

    Friday, May 7, 2010 8:39 AM
  • Then please post a simplified example of your classes, so we can have a look at it :)

     

    Friday, May 7, 2010 10:39 AM
  • Hi,

    Any update of this case?   Could you please tell us how is the problem now? 

     

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    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, May 12, 2010 1:14 AM
    Moderator
  • Hi,

    Sorry, I'm really busy lately. Haven't been able to reply. So the simplified version of the classes are:

    Public Class GlobalSearchItem
    
        Private _companyIDs As List(Of Integer?)
    
        Public Property CompanyIds() As List(Of Integer?)
          Get
            Return _companyIDs
          End Get
          Set(ByVal value As List(Of Integer?))
            _companyIDs = value
          End Set
        End Property
    
    End Class
    
    Public Class Refinement
    
        Private _dataID As Int32
    
        Public Property DataId() As Integer
          Get
            Return _dataID
          End Get
          Set(ByVal value As Integer)
            _dataID = value
          End Set
        End Property
    
    End Class
    
    Private Function GetCompanyRefinements(Byval allRefinements As List(Of Refinement), _
    					 Byval result As List(Of GlobalSearchItem)) As List(Of Refinement)
    
    ' So you can see that in this function I am being passed a list of Refinement and a list of GlobalSearchItem.
    ' I need to use linq to somehow join on DataId and CompanyIds.
    
    End Function

    So, within the GetCompanyRefinements function I need to join allRefinements to result and return a list of Refinement.

    Thanks for you time :)

    Tuesday, May 18, 2010 8:08 AM
  • Sounds like what you are looking for is the 'SelectMany' operator. E.g.:

    from r in refinements
    where (searchItems.SelectMany(s => s.CompanyIDs).Contains(r.DataID))
    select r;

    .


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Tuesday, May 18, 2010 9:02 AM
    Answerer