NullReferenceException Query SQLite database with Where on a concatenated string property

Question

I'm trying to select a record using the following code:

Location item = connection
  .Table<Location>()
  .Where(l => l.Label.Equals(label))
  .FirstOrDefault();

This results in:

System.NullReferenceException: Object reference not set to an instance of an object.

When I try the same, on a different property (Postcode), it all works fine also when no records are found.:

Location item = connection
  .Table<Location>()
  .Where(l => l.Postcode.Equals(label))
  .FirstOrDefault();

This is the Location Class:

// These are the Locations where the Stock Take Sessions are done
public class Location : DomainModels, IComparable<Location>
{
    [JsonProperty("id"), PrimaryKey]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Street { get; set; }
    public int Number { get; set; }
    public string Postcode { get; set; }
    public string City { get; set; }
    public bool Completed { get; set; }

    [Ignore] // Removing this does not have an impact on the NullReferenceException
    public string Label => $"{Name ?? ""} - ({Postcode ?? ""})";

    public int CompareTo(Location other)
    {
        return Name.CompareTo(other.Name);
    }

    // Navigation property
    // One to many relationship with StockItems
    [OneToMany(CascadeOperations = CascadeOperation.All), Ignore]
    public List<StockItem> StockItems { get; set; }

    // Specify the foreign key to StockTakeSession
    [ForeignKey(typeof(StockTakeSession))]
    public int StockTakeSessionId { get; set; }

    // One to one relationship with StockTakeSession
    [OneToOne]
    public StockTakeSession StockTakeSession { get; set; }

}

What am I doing wrong?

Thanks for any suggestions!

one of the `Location` in `connection.Table` has `Label` = null. That's why you have that issue. You need to make sure that all the items in the `connection.Table` has Label property set. — Chetan, Sep 22 '17 at 10:34
Possible duplicate of [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — waka, Sep 22 '17 at 10:35
@ChetanRanpariya: I've checked the database and none of the records is missing de name or postcode. — Robert, Sep 22 '17 at 11:06

Igor · Accepted Answer · 2017-09-22T11:08:34.223

Your where filters in the data store on Label but your markup on your class Location has decorated the Label property with IgnoreAttribute. This means the Label property will not be set until after the entity has been materialized to memory and you can't do anything with it in the data store.

.Where(l => l.Label.Equals(label))

Fixes

There are some options.

You could set this to computed and create a computed column in the store with that same logic. This involves manually changing your table schema either directly in your RDBMS manager or editing your migration scripts. The property gets marked with [DatabaseGenerated(DatabaseGeneratedOption.Computed)] (if using attributes, which your code above is).
You could change the Where to filter on the Properties that compose Label that are found in the store. ie: .Where(l => l.Postcode.Equals(Postcode) && l.Name.Equals(Name))
You could materialize everything before that particular filter to memory and then apply the filter. This is not recommended if everything up to that point leads to a lot of records. Example, with the code below if the table is large you would be retrieving everything for a single record.
```
Location item = connection
  .Table<Location>()
  .AsEnumerable()
  .Where(l => l.Label.Equals(label))
  .FirstOrDefault();
```

Edit

[Ignore] // Removing this does not have an impact on the NullReferenceException

No, it should not unless you go through and add the column with the same name to your existing schema and populate it with all data. (or create a computed column in your schema with the same name)

Thank you @Igor. Can you please show me the code you suggest for the first and third bullet? I am to new with this to fully understand what you mean and a code example might help. Thanks! — Robert, Sep 22 '17 at 11:03
@Robert - done. I did not add code for how to create a calculated column in your data store but you can find that online with a search. — Igor, Sep 22 '17 at 11:09
I was going for the first option, but this seems to be Entity Framework related. Am I right? I am using SQLite-Net with SQLite.Net.Attributes and SQLiteNetExtensions.Attributes. Both do not support the [DatabaseGenerated] attribute. — Robert, Sep 22 '17 at 11:53
@Robert - I know almost nothing of `sql-lite` but if that rdbms supports computed columns it should work. The attribute is provider agnostic: [DatabaseGeneratedAttribute](https://msdn.microsoft.com/en-us/library/system.componentmodel.dataannotations.schema.databasegeneratedattribute(v=vs.110).aspx) — Igor, Sep 22 '17 at 11:55
No problem, thank you for your time. To solve this for now, I will just generate the label when the record is inserted into the DataBase. — Robert, Sep 22 '17 at 12:02

NullReferenceException Query SQLite database with Where on a concatenated string property

1 Answers1

Fixes

Edit