[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 918
  • Last Modified:

VB LINQ query changes data type and I don't know why!

Happy New Year Experts,

I have two tables (some columns omitted);

Table "tblAccountPriceOffer" contains customer specific pricing.  Pricing can vary over time and I use the latest DateTime to get the current price for customer X.  Here are some of the columns;
ID (PK, bigint)
DateTime (datetime)
AccountNbr (int)
Price (money)

Table "tblC" list all customers.  Some customers are part of a group (which allows for alternate pricing - a seperate table not listed here).  Here are some of the columns;
ID (PK, bigint)
DateTime (datetime)
AccountNbr (int)
GroupNbr (int)

I used the O/R Designer and tried (unsuccessfully) to query table tblAccountPriceOffer for all records which have an AccountNbr value matching any AccountNbr in table tblC where the GroupNbr is X.  For example, there might be three rows in tblC where GroupNbr = 12 (e.g. AccountNbr's 101, 107 & 294).  I have a web page which uses a ListView control to display tbltblAccountPriceOffer.  I have a Textbox which I use to filter by AccountNbr.  I want to add a Textbox to filter by GroupNbr.  If I entered a "12" in the Textbox, the ListView should only show three rows.  Here is my code;

        Dim db As New MyDatabaseDataContext()
        'get all records for ListViewFAQ
        Dim qA As IEnumerable(Of tblAccountPriceOffer) = db.tblAccountPriceOffers 'qA = {System.Data.Linq.Table(Of tblAccountPriceOffer)}
        'filter by ddl for AccountNbr
        If qA.Any() AndAlso Not Me.txtAccountNbr.Text = String.Empty Then
            qA = qA.Where(Function(s) s.AccountNbr = Me.txtAccountNbr.Text)
        End If
         'filter by ddl for GroupNbr
       If qA.Any() AndAlso Not Me.txtGroupNbr.Text = String.Empty Then
            Dim qP As IEnumerable(Of tblC) = db.tblCs.Where(Function(p) p.GroupNbr = CInt(Me.txtGroupNbr.Text)) 'qP = Database Query
            qA = qA.Where(Function(a) a.AccountNbr = qP.Where(Function(b) b.AccountNbr = a.AccountNbr).FirstOrDefault.AccountNbr) 'qA changes to "In-Memory Query" on this line and exception "System.NullReferenceException: Object reference not set to an instance of an object." is thrown
        End If
        'bind to ListView
        Me.ListView1.DataSource = qA.ToArray()
        Me.ListView1.DataBind()

Thank you for taking the time to read my question.  I look forward to your comments.
0
thefridgeVFA
Asked:
thefridgeVFA
  • 7
  • 5
1 Solution
 
halceyonCommented:
Is your Column called "DateTime"?  If so, this could case some hassles with Linq.  I had a similar problem when I had a table called "System"  Try renaming the column.
0
 
thefridgeVFAAuthor Commented:
Oops - typo.  It is "ZuluDataTime" in both tables.  Sorry about that.
0
 
Fernando SotoCommented:
Hi thefridgeVFA;

I suspect that this is the line that is causing the issue.

qA = qA.Where(Function(a) a.AccountNbr = qP.Where(Function(b) b.AccountNbr = _
        a.AccountNbr).FirstOrDefault.AccountNbr)

The first thing I believe that is causing a problem is that you have a Where clause which has two Lambda expressions in it where one is only allowed. The other would be that you are using a local variable inside Function(b), a, which would not be available outside of that function. In Visual Basic you can not have a function defined inside another function.

If you describe what you wish to accomplish at this point I will attempt to help.

Fernando
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Fernando SotoCommented:
Hi thefridgeVFA;

Are you getting any exceptions or run-time errors?

Fernando
0
 
thefridgeVFAAuthor Commented:
Yes, the error occurs in that line.  I commented the code in my question above with this error and a few other observations (repeated below for your convenience);
'qA = {System.Data.Linq.Table(Of tblAccountPriceOffer)}
'qP = Database Query
'qA changes to "In-Memory Query" on this line and exception "System.NullReferenceException: Object reference not set to an instance of an object." is thrown.

I tried to get the same sequence via a query expression (instead of dot notation) using this;
  qA = From a In qA _
           Let accts = (From p In db.tblProcessors Where p.GroupNbr = CInt(Me.txtGroupNbr.Text)) _
           Where (accts.Count > 0 And accts.Any(Function(g) g.AccountNbr = a.AccountNbr)) _
           Select a.ID, a.AccountNbr, a.OneMonth, a.ThreeMonth, a.TwelveMonth
but I got this error;
System.InvalidCastException: Unable to cast object of type 'WhereSelectEnumerableIterator`2[VB$AnonymousType_2`2[tblAccountPriceOffer,System.Linq.IQueryable`1[tblC]],VB$AnonymousType_3`5[System.Int64,System.Int32,System.Decimal,System.Decimal,System.Decimal]]' to type 'System.Collections.Generic.IEnumerable`1[tblAccountPriceOffer]'.

There are actually three pricing columns in tblAccountPriceOffer, but I omitted them here for brevity.

I want to query table tblAccountPriceOffer for all records which have an AccountNbr value matching any AccountNbr in table tblC where the GroupNbr is X.  For example, there might be three rows in tblC where GroupNbr = 12 (e.g. AccountNbr's 101, 107 & 294).  I have a web page which uses a ListView control to display tbltblAccountPriceOffer.  I have a Textbox which I use to filter by AccountNbr.  I want to add another Textbox to filter by GroupNbr (which is not a property of tblAccountPriceOffer).  If I entered a "12" in the Group Textbox, the query would need to return a sequence of all tblCs which have 12 (int) in the GroupNbr property.  Then the query needs to compare each tblAccountPriceOffer element's AccountNbr property for a match against the AccountNbr property of any of the elements in the tblC sequence.  Using my example, the ListView should only show three rows.
0
 
thefridgeVFAAuthor Commented:
In the name of brevity, I redacted my code.  In my haste to reply, I failed to properly redact my recent comment.  The query above should read;

  qA = From a In qA _
           Let accts = (From p In db.tblC Where p.GroupNbr = CInt(Me.txtGroupNbr.Text)) _
           Where (accts.Count > 0 And accts.Any(Function(g) g.AccountNbr = a.AccountNbr)) _
           Select a.ID, a.AccountNbr, a.Price

I also wrote "tbltblAccountPriceOffer" in the last paragraph (should be tblAccountPriceOffer).

Sorry about that.
0
 
thefridgeVFAAuthor Commented:
I suppose another approach might be to append the GroupNbr property to qA.  I am going to try that approach while you digest my prior comments.  Please let me know if you need anything else.
0
 
Fernando SotoCommented:
Hi thefridgeVFA;
Try removing this line of code
       
Dim qA As IEnumerable(Of tblAccountPriceOffer) = db.tblAccountPriceOffers       

And modifying your last query as follows:

Dim qA = From a In db.tblAccountPriceOffer
         Let accts = (From p In db.tblC Where p.GroupNbr = CInt(Me.txtGroupNbr.Text)) _
         Where (accts.Count > 0 And accts.Any(Function(g) g.AccountNbr = a.AccountNbr)) _
         Select a.ID, a.AccountNbr, a.Price       

Open in new window

0
 
thefridgeVFAAuthor Commented:
FernandoSoto;

Thanks for the quick reply.  I got this to work;
        Dim db As New MyDatabaseDataContext()
        'get all records from tblAccountPriceOffers for ListView1
        Dim qA As IQueryable = db.tblAccountPriceOffers
        'filter by ddl's for GroupNbr & AccountNbr
        'first, filter by GroupNbr
        If Not Me.txtGroupNbr.Text = String.Empty Then
            'find all AccountNbrs in this GroupNbr
            qA = From a In db.tblAccountPriceOffers _
                 Let accts = (From p In db.tblCs Where p.GroupNbr = CInt(Me.txtGroupNbr.Text)) _
                 Where (accts.Count > 0 And accts.Any(Function(g) g.AccountNbr = a.AccountNbr)) _
                 Select a.ID, a.ZuluDateTime, a.Price
        End If
        'second, filter by AccountNbr
        If qA.Cast(Of IEnumerable(Of tblAccountPriceOffer)).Any() And Not Me.txtAccountNbr.Text = String.Empty Then
            qA = db.tblAccountPriceOffers.Where(Function(s) s.AccountNbr = Me.txtAccountNbr.Text)
        End If
        'bind to ListView
        Me.ListView1.DataSource = qA.AsQueryable
        Me.ListView1.DataBind()

I am interested in your thoughts regarding why this works.

FYI I am halfway through reading "Pro LINQ, Language Integrated Query in VB 2008" by Rattz & Hayes.  Your explanations really help me understand the concepts they discuss.
0
 
thefridgeVFAAuthor Commented:
Oops - another typo.  Note change in last line (returned one more property);
         qA = From a In db.tblAccountPriceOffers _
                 Let accts = (From p In db.tblCs Where p.GroupNbr = CInt(Me.txtGroupNbr.Text)) _
                 Where (accts.Count > 0 And accts.Any(Function(g) g.AccountNbr = a.AccountNbr)) _
                 Select a.ID, a.ZuluDateTime, a.AccountNbr, a.Price
0
 
Fernando SotoCommented:
Hi thefridgeVFA;

I placed comments in your code. If there is something I missed that you would like me to try and explained please let me know I will attempt to comment on it.

Fernando
Dim db As New MyDatabaseDataContext()

'get all records from tblAccountPriceOffers for ListView1
==============================================================
' This following line really does nothing because it gets 
' over written in one of the two if statements below or if
' none of the two if statements do not get executed returns 
' the complete table. Leave it in if that is what you wanted.
Dim qA As IQueryable = db.tblAccountPriceOffers
==============================================================

'filter by ddl's for GroupNbr & AccountNbr
'first, filter by GroupNbr
If Not Me.txtGroupNbr.Text = String.Empty Then
    'find all AccountNbrs in this GroupNbr
    ===========================================================================================
    ' This following query works because the variable qA is defined as a IQueryable and any return
    ' set from a SQL Linq query is of type IQueryable. Unlike a previous version you posted which
    ' had qA as of type Table(Of tblAccountPriceOffer), the select returns 4 fields which is an 
    ' anonymous type and not a tblAccountPriceOffer type. The let statement creates a sub query
    ' to the main query which starts "From a ..."
    qA = From a In db.tblAccountPriceOffers _
         Let accts = (From p In db.tblCs Where p.GroupNbr = CInt(Me.txtGroupNbr.Text)) _
         Where (accts.Count > 0 And accts.Any(Function(g) g.AccountNbr = a.AccountNbr)) _
         Select a.ID, a.ZuluDateTime, a.AccountNbr, a.Price
    ===========================================================================================
End If

'second, filter by AccountNbr
If qA.Cast(Of IEnumerable(Of tblAccountPriceOffer)).Any() And Not Me.txtAccountNbr.Text = String.Empty Then
   qA = db.tblAccountPriceOffers.Where(Function(s) s.AccountNbr = Me.txtAccountNbr.Text)
End If
'bind to ListView
Me.ListView1.DataSource = qA.AsQueryable
Me.ListView1.DataBind()

Open in new window

0
 
thefridgeVFAAuthor Commented:
FernadoSoto,

Thank you for another awesome answer.  I will award you the points.
Regarding the first line, I do want the entire table IF the filtering textboxes are left empty.

Is there any way to make this query work if qA was of Type IEnumerable, like this;
Dim qA As IEnumerable(Of tblAccountPriceOffer) = db.tblAccountPriceOffers
     'qA = {System.Data.Linq.Table(Of tblAccountPriceOffer)}
 
0
 
Fernando SotoCommented:
Hi thefridgeVFA;

This statement should work fine.

Dim qA As IEnumerable(Of tblAccountPriceOffer) = db.tblAccountPriceOffers

As I stated before the return set from a Linq to SQL query is a IQueryable collection. But IQueryable inherits from the base class  IEnumerable so all IQueryable collections can be cast to an IEnumerable.

Fernando
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now