none
How can I test whether a value is contained in an array in a linq query?

    Question

  • Previously we were comparing a range (user chose a high and a low value, and anything within that range was a 

    match). The query was like:

    IQueryable<CompletePlatypusEvent> QPlatypuss = (from tCompletePlatypusEvent in 
    context.CompletePlatypusEvent
                                      where tCompletePlatypusEvent.PlatypusEventEventStartDateTime <= result.dtpEnd
                                            && tCompletePlatypusEvent.PlatypusEventEventStartDateTime >= result.dtpBegin
                                            && (result.PlatypusEventID != -1 ? tCompletePlatypusEvent.JournalReportID == 
    result.PlatypusEventID : true)
                                            && (result.RegisterID != -1 ? tCompletePlatypusEvent.PlatypusEventRegisterID == 
    result.RegisterID : true)
                                            && (result.CashierID.Length > 0 ? tCompletePlatypusEvent.PlatypusEventCashierID == 
    result.CashierID : true)
                                            && (result.TillID.Length > 0 ? tCompletePlatypusEvent.PlatypusEventTillID == result.TillID : true)
                                            && (result.UpcCnt > 0 ? result.UPCs.Contains(tCompletePlatypusEvent.ItemCodePOSCode) : 
    true)
                                            && (result.PlatypussTotalMin != -1 ? 
    tCompletePlatypusEvent.TSTransactionTotalNetAmount_Value >= result.PlatypussTotalMin : true)
                                            && (result.PlatypussTotalMax != -1 ? 
    tCompletePlatypusEvent.TSTransactionTotalNetAmount_Value <= result.PlatypussTotalMax : true)                                   
                                            && ((result.BeginDept == -1 || tCompletePlatypusEvent.ItemLineDuckbillCodeInt >= 
    result.BeginDept) || 
                                                (result.BeginDept == -1 || tCompletePlatypusEvent.DuckbillCodeLineDuckbillCodeInt >= 
    result.BeginDept))
                                            && ((result.EndDept == -1 || tCompletePlatypusEvent.ItemLineDuckbillCodeInt <= 
    result.EndDept) || 
                                                (result.EndDept == -1 || tCompletePlatypusEvent.DuckbillCodeLineDuckbillCodeInt <= 
    result.EndDept))
                                      select tCompletePlatypusEvent);

    The "interesting" part is the "BeginDept" and "EndDept" stuff near the end. This now has to compare 
    ItemLineDuckbillCodeInt and DuckbillCodeLineDuckbillCodeInt to an array. How can this be done in a LINQ query?

    IOW, the logic is more like:

    tCompletePlatypusEvent.ItemLineDuckbillCodeInt in (result.DeptArray) ||
     tCompletePlatypusEvent.DuckbillCodeLineDuckbillCodeInt in (result.DeptArray);



    Wednesday, July 17, 2013 11:05 PM

Answers

  • You have the right parentheses in the wrong position for the first statement should be as the following.

    && result.DeptArray.Contains(tCompleteSaleEvent.ItemLineMerchandiseCodeInt.Value) || result.DeptArray.Contains(tCompleteSaleEvent.MerchandiseCodeLineMerchandiseCodeInt.Value)


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Monday, July 22, 2013 6:50 PM

All replies

  • Do you have a T-SQL question?

    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration


    Thursday, July 18, 2013 12:34 AM
  • A SQL question. I don't think it should be too different form LINQ.
    Thursday, July 18, 2013 12:46 AM
  • Just purely with quick sight inspection of the posted LINQ code using my feeble biological intelligence I don't see much resemblance to T-SQL code.

    Of course the coming Artificial Intelligence will be able to pick up the commonality in T-SQL & LINQ in a fraction of a second.

    I am moving this thread to LINQ.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration


    Thursday, July 18, 2013 12:54 AM
  • Thanks; that works, too.
    Thursday, July 18, 2013 1:15 AM
  • Hi B. Clay Shannon;

    Try it like this to achieve the T-SQL IN function.

    result.DeptArray.Contains(tCompletePlatypusEvent.ItemLineDuckbillCodeInt) ||
    result.DeptArray.Contains(tCompletePlatypusEvent.DuckbillCodeLineDuckbillCodeInt);


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Thursday, July 18, 2013 3:06 PM
  • Hi B. Clay Shannon;

    Try it like this to achieve the T-SQL IN function.

    result.DeptArray.Contains(tCompletePlatypusEvent.ItemLineDuckbillCodeInt) ||
    result.DeptArray.Contains(tCompletePlatypusEvent.DuckbillCodeLineDuckbillCodeInt);


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Nope, that fails with: 'long[]' does not contain a definition for 'Contains' and the best extension method overload 'System.Linq.Queryable.Contains<TSource>(System.Linq.IQueryable<TSource>, TSource)' has some invalid arguments.

    Thursday, July 18, 2013 10:57 PM
  • Humm, I just built a long[] and tested it with the Contains method and it worked fine. Can you post the class that represents the result object. Thanks.

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Friday, July 19, 2013 6:23 PM
  • Humm, I just built a long[] and tested it with the Contains method and it worked fine. Can you post the class that represents the result object. Thanks.

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    IQueryable<CompleteSaleEvent> QSales = (from tCompleteSaleEvent in context.CompleteSaleEvent
                                      where tCompleteSaleEvent.SaleEventEventStartDateTime <= result.dtpEnd
                                            && tCompleteSaleEvent.SaleEventEventStartDateTime >= result.dtpBegin
                                            && (result.SaleEventID != -1 ? tCompleteSaleEvent.JournalReportID == result.SaleEventID : true)
                                            && (result.RegisterID != -1 ? tCompleteSaleEvent.SaleEventRegisterID == result.RegisterID : true)
                                            && (result.CashierID.Length > 0 ? tCompleteSaleEvent.SaleEventCashierID == result.CashierID : true)
                                            && (result.TillID.Length > 0 ? tCompleteSaleEvent.SaleEventTillID == result.TillID : true)
                                            //&& tCompleteSaleEvent.SaleEventSaleEventTypeID == 1
                                            && (result.UpcCnt > 0 ? result.UPCs.Contains(tCompleteSaleEvent.ItemCodePOSCode) : true)
    
                                            && (result.SalesTotalMin != -1 ? tCompleteSaleEvent.TSTransactionTotalNetAmount_Value >= result.SalesTotalMin : true)
                                            && (result.SalesTotalMax != -1 ? tCompleteSaleEvent.TSTransactionTotalNetAmount_Value <= result.SalesTotalMax : true)
                                            //&& (result.SalesTotalMax != -1 ? tCompleteSaleEvent.TSTransactionTotalNetAmount_Value < result.SalesTotalMax : true)
                                          
                                            //&& ((result.BeginDept == -1 || tCompleteSaleEvent.ItemLineMerchandiseCodeInt >= result.BeginDept) || 
                                            //    (result.BeginDept == -1 || tCompleteSaleEvent.MerchandiseCodeLineMerchandiseCodeInt >= result.BeginDept))
                                            //&& ((result.EndDept == -1 || tCompleteSaleEvent.ItemLineMerchandiseCodeInt <= result.EndDept) || 
                                            //    (result.EndDept == -1 || tCompleteSaleEvent.MerchandiseCodeLineMerchandiseCodeInt <= result.EndDept))
    
                                            // According to an msdn post answer, I need to add something like the below, but it would not compile...; 
                                            // ... still, need to include the array *somehow*...
                                            //&& result.DeptArray.Contains(tCompleteSaleEvent.ItemLineMerchandiseCodeInt) ||
                                            //   result.DeptArray.Contains(tCompleteSaleEvent.MerchandiseCodeLineMerchandiseCodeInt)
                                      select tCompleteSaleEvent); BTW, are you the Fernando from the Abba song?
    
            public ObjectSet<CompleteSaleEvent> CompleteSaleEvent
            {
                get
                {
                    if ((_CompleteSaleEvent == null))
                    {
                        _CompleteSaleEvent = base.CreateObjectSet<CompleteSaleEvent>("CompleteSaleEvent");
                    }
                    return _CompleteSaleEvent;
                }
            }
            private ObjectSet<CompleteSaleEvent> _CompleteSaleEvent;
    
            [EdmScalarPropertyAttribute(EntityKeyProperty=true, IsNullable=false)]
            [DataMemberAttribute()]
            public global::System.DateTime SaleEventEventStartDateTime
            {
                get
                {
                    return _SaleEventEventStartDateTime;
                }
                set
                {
                    if (_SaleEventEventStartDateTime != value)
                    {
                        OnSaleEventEventStartDateTimeChanging(value);
                        ReportPropertyChanging("SaleEventEventStartDateTime");
                        _SaleEventEventStartDateTime = StructuralObject.SetValidValue(value);
                        ReportPropertyChanged("SaleEventEventStartDateTime");
                        OnSaleEventEventStartDateTimeChanged();
                    }
                }
            }
            private global::System.DateTime _SaleEventEventStartDateTime;
            partial void OnSaleEventEventStartDateTimeChanging(global::System.DateTime value);
            partial void OnSaleEventEventStartDateTimeChanged();
    


    Friday, July 19, 2013 6:50 PM
  • Hi;

    Abba song, no not me.

    No, the code I am looking for is the class definition for the object you instantiated in the variable result.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Friday, July 19, 2013 7:30 PM
  • Hi;

    Abba song, no not me.

    No, the code I am looking for is the class definition for the object you instantiated in the variable result.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Do you mean:

    public partial class CompleteSaleEvent : EntityObject

    ? It is hundreds of lines long, and I shouldn't post it publicly, anyway. If that's what you mean, and you want to see it, I can email it to you.

    Friday, July 19, 2013 8:39 PM
  • To your statement, "Do you mean: public partial class CompleteSaleEvent : EntityObject", I do not know because the declaration for the variable result is not posted in any of the above code. In your code you should have a declaration something like this, MyType result = new MyType();, In this example I am looking for the MyType class. If it is too much to post here then post the variable DeptArray and any accessor it may have.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Friday, July 19, 2013 8:52 PM
  • To your statement, "Do you mean: public partial class CompleteSaleEvent : EntityObject", I do not know because the declaration for the variable result is not posted in any of the above code. In your code you should have a declaration something like this, MyType result = new MyType();, In this example I am looking for the MyType class. If it is too much to post here then post the variable DeptArray and any accessor it may have.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    I still don't know just what you're looking for (I didn't write this code, and it seems like geekified Greek to me), but here's what follows the LINQ query:

                        List<long> IDs = (QSales.Select(t => t.SaleEventID).Distinct()).ToList();
    
                        var res = (from tSaleEvent2 in context.SaleEvent
                                   where IDs.Contains(tSaleEvent2.ID)
                                   select tSaleEvent2);
    
                        res = res.OrderBy(t => t.EventStartDateTime);
    
                        int ct = res.Count();
                        if (ct > result.MaxResults)
                        {
                            
                            res = res.Take(result.MaxResults);
                            result.HasMaxResults = true;
                            result.HasMessage = true;
                            result.Message = String.Format("The criteria entered produced {0} results.  The result set has been capped at a maximum of {1} receipts.", ct, result.MaxResults);
                        }
    
                        ids = res.Select(t => t.ID).ToList();
    
    ...all the DeptArray code:
    
            [DataMember]
            public long[] DeptArray { get; set; }
    
                        // TODO: Remove this random assignment of vals; assign them in Receipt Criteria legitimately (based on what the user selects)
                        result.DeptArray = new long[] { 2, 3, 4, 5, 6, 7, 8, 9, 10 };
    


    Friday, July 19, 2013 9:27 PM
  • OK, lets try this, what is the SQL Server data type of the following in the database

    1.  tCompletePlatypusEvent.ItemLineDuckbillCodeInt
    2.  
    tCompletePlatypusEvent.DuckbillCodeLineDuckbillCodeInt


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Saturday, July 20, 2013 1:11 AM
  • OK, lets try this, what is the SQL Server data type of the following in the database

    1.  tCompletePlatypusEvent.ItemLineDuckbillCodeInt
    2.  
    tCompletePlatypusEvent.DuckbillCodeLineDuckbillCodeInt


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Those types are actually:

    [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
            [DataMemberAttribute()]
            public Nullable<global::System.Int32> ItemLineMerchandiseCodeInt
    
    [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
            [DataMemberAttribute()]
            public Nullable<global::System.Int32> MerchandiseCodeLineMerchandiseCodeInt
    

    ...I was disguising their real names with the "Duckbill" and "Platypus" stuff because at my previous job, the powers that be/were were very paranoid about any of their sql statements, etc., being posted online. I'm pretty much over that now, though.
    Monday, July 22, 2013 3:31 PM
  • Hi B Clay Shannon;

    Well it is starting to make sense now. In the database you have the two items in the query with issues defined as Nullable of type integer and the array you are trying to see if a value is contained within it is a long[] and what is causing the issue. So if I am correct the following should correct the issue.

    result.DeptArray.Contains(tCompletePlatypusEvent.ItemLineDuckbillCodeInt.Value) ||
    result.DeptArray.Contains(tCompletePlatypusEvent.DuckbillCodeLineDuckbillCodeInt.Value);

    See if that gets you pass the current issue.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Monday, July 22, 2013 6:25 PM
  • Hi B Clay Shannon;

    Well it is starting to make sense now. In the database you have the two items in the query with issues defined as Nullable of type integer and the array you are trying to see if a value is contained within it is a long[] and what is causing the issue. So if I am correct the following should correct the issue.

    result.DeptArray.Contains(tCompletePlatypusEvent.ItemLineDuckbillCodeInt.Value) ||
    result.DeptArray.Contains(tCompletePlatypusEvent.DuckbillCodeLineDuckbillCodeInt.Value);

    See if that gets you pass the current issue.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Changing this code:

    && result.DeptArray.Contains(tCompleteSaleEvent.ItemLineMerchandiseCodeInt) ||
                                               result.DeptArray.Contains(tCompleteSaleEvent.MerchandiseCodeLineMerchandiseCodeInt)
    

    ...to:

    && result.DeptArray.Contains(tCompleteSaleEvent.ItemLineMerchandiseCodeInt).Value ||                                         result.DeptArray.Contains(tCompleteSaleEvent.MerchandiseCodeLineMerchandiseCodeInt.Value)
    

    ...I now get these two err msgs on the first line:

    0) Instance argument: cannot convert from 'long[]' to 'System.Linq.IQueryable<int?>'

    1) 'long[]' does not contain a definition for 'Contains' and the best extension method overload 'System.Linq.Queryable.Contains<TSource>(System.Linq.IQueryable<TSource>, TSource)' has some invalid arguments
    Monday, July 22, 2013 6:45 PM
  • You have the right parentheses in the wrong position for the first statement should be as the following.

    && result.DeptArray.Contains(tCompleteSaleEvent.ItemLineMerchandiseCodeInt.Value) || result.DeptArray.Contains(tCompleteSaleEvent.MerchandiseCodeLineMerchandiseCodeInt.Value)


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Monday, July 22, 2013 6:50 PM
  • Wow, that's my second really dumb mistake today (third, if you count getting out of bed).
    Monday, July 22, 2013 7:01 PM
  • LOL, As I always say there are no dumb mistakes as long as we learn from them. Have a great day.

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Monday, July 22, 2013 7:04 PM