0

My goal is to execute a linq to sql query that will return a value from a varbinary(max) database field if the varbinary(max) field is not null. In the code below, x.doc is a varbinary(max) in the database.

The basics of my code is this:

    var pdfquery = from x in dataContext.Statements
         where x.enccc == card && x.stDate == datetime      
         select x.doc;

                    if (pdfquery.Count() == 1 )
                    {
                        pdffile = pdfquery.FirstOrDefault().ToArray(); 
                    }
                    else
                    {
                     //go to a webservice to get pdffile and write it to the db
                     }

The code block returned a null value because the if statement was true. There is a null value in the database when the two parameters are passed.

Where the section of "pdfquery.Count() == 1" is, I've tried:

  • if (pdfquery.Any())
  • if (pdfquery.FirstOrDefault() != null)
  • if (pdfquery.FirstOrDefault().toArray().Length > 0)

    • All of those are giving me a Null Value Exception.

    What am I missing? How do I identify when a query that returns a null value varbinary(max) so that I can take appropriate action?

    Update (07/17/14): I decided to handle the Null Refernece Exception with a try catch:

         try
                    {
                        var pdfquery = from x in dataContext.Statements
                                       where x.enccc == card && x.stDate == datetime
                                       select x.doc;

                        pdffile = pdfquery.SingleOrDefault().ToArray(); //gets the binary data and converts it to a byte array

                    }
                    catch(NullReferenceException nux)
                    {
                        logger.LogDebug("No Binary Data Exists for Statement, making  Request ---- ",nux);
                        getStatment(unencArray); 
                        getByteArray(statementxml);  
                        writeByteArrayToDb(unencArray, pdffile); 

                    }

    I don't really like doing this, because I'd rather be able to catch other exceptions if they're thrown. I'm going to try changing my Byte[] to ?Byte[] in hopes that the null value will be handled normally.

    David
    • 15
    • 12
    • I really doubt that pdfquery is null. I think the NullReferenceException occurs while the query is being executed – Selman Genç Jul 14 '14 at 21:27
    • @Selman22 Ah I see now. pdfQuery is an IQueryable at this point. – vcsjones Jul 14 '14 at 21:28
    • @Selman22, pdfquery hits, but the value of x.doc is null. I think there may be two possible outcomes - one where there query has no hits and is null, or one where the value returned by the query is null. I'm trying to identify the latter. – David Jul 14 '14 at 21:33

    1 Answers1

    0

    Just add it to the where clause

    var pdfquery = from x in dataContext.CcStatements
               where x.enccc == card && x.stDate == datetime && x.doc != null      
               select x.doc;

    also if you are expecting just one result use SingleOrDefault (as opposed to FirstOrDefault). And finally if you use Count() like this you will make one unnecessary query for Count because the query will be executed once for Count and once for the actual data.

    Stilgar
    • 22,354
    • 14
    • 64
    • 101