Xamarin.Forms PCL项目SQLite报错：无法识别List<int>与Attendance类型

Hey there! Let's break down why you're hitting those Don't know about... errors and how to fix them.

The Root Cause #

SQLite is a relational database, and the SQLite-net library you're using doesn't natively support mapping collection types (like List<int>) or custom reference types (like your Attendance class) directly as columns in a table. It only knows how to handle basic value types (int, string, DateTime, etc.) and simple objects that map directly to table columns.

Solutions for Your Two Problem Types #

1. Handling List<int> in Your Info Class

You have two solid options here, depending on your use case:

Option A: Use a Join Table (Relational Approach)

This is the "proper" relational database pattern for one-to-many relationships. Create a separate table to store each integer in the list, linked to your Info record via a foreign key.

• First, define the join table:

  public class InfoIntValue
  {
      [PrimaryKey, AutoIncrement]
      public int Id { get; set; }
      public int InfoId { get; set; } // Foreign key to Info.Id
      public int Value { get; set; } // The integer from your list
  }
  

• Update your Info class to ignore the list (since we'll store it separately):

  public class Info
  {
      [PrimaryKey, AutoIncrement]
      public int Id { get; set; }
      // Your other existing properties...
      
      [Ignore] // Tell SQLite-net to skip this property for table mapping
      public List<int> MyIntList { get; set; } = new List<int>();
  }
  

• Don't forget to create the new table on initialization:

  database.CreateTableAsync<InfoIntValue>().Wait();
  

• When saving an Info record:
  1. Save the Info first to get its Id
  2. Insert a new InfoIntValue for each integer in MyIntList, setting InfoId to the saved Info's Id
• When loading an Info record:
  1. Fetch the Info from the database
  2. Query all InfoIntValue records where InfoId matches the Info's Id, then collect the Values into MyIntList

Option B: Serialize the List to JSON (Simpler Approach)

If you don't need to query individual integers in the list, you can serialize the List<int> to a string and store it as a single column in the Info table.

• Use a library like Newtonsoft.Json to handle serialization. Update your Info class:

  public class Info
  {
      [PrimaryKey, AutoIncrement]
      public int Id { get; set; }
      // Your other existing properties...
      
      [Ignore]
      public List<int> MyIntList { get; set; } = new List<int>();
      
      // This property stores the serialized JSON string
      public string MyIntListJson
      {
          get => JsonConvert.SerializeObject(MyIntList);
          set => MyIntList = string.IsNullOrEmpty(value) ? new List<int>() : JsonConvert.DeserializeObject<List<int>>(value);
      }
  }
  

• No extra tables needed here—SQLite-net will map the MyIntListJson string column automatically, and the MyIntList property will handle serialization/deserialization behind the scenes.

2. Handling the Attendance Type in Your Info Class

Again, two options based on whether Attendance is a standalone entity or a dependent part of Info:

Option A: Use a Foreign Key (Standalone Attendance)

If Attendance is its own entity (you already created its table!), store a foreign key to the Attendance record in your Info table instead of the full object:

• Update your Info class:

  public class Info
  {
      [PrimaryKey, AutoIncrement]
      public int Id { get; set; }
      // Your other existing properties...
      
      [Ignore]
      public Attendance MyAttendance { get; set; }
      
      // Foreign key to Attendance.Id
      public int? AttendanceId { get; set; }
  }
  

• When saving: Save the Attendance first, then set Info.AttendanceId to the saved Attendance's Id before saving Info
• When loading: Fetch the Info, then query the Attendance table using AttendanceId to populate MyAttendance

Option B: Serialize Attendance to JSON (Dependent Object)

If Attendance only exists as part of an Info record and doesn't need its own table, serialize it to a string column just like the list:

• Update your Info class:

  public class Info
  {
      [PrimaryKey, AutoIncrement]
      public int Id { get; set; }
      // Your other existing properties...
      
      [Ignore]
      public Attendance MyAttendance { get; set; }
      
      public string AttendanceJson
      {
          get => JsonConvert.SerializeObject(MyAttendance);
          set => MyAttendance = string.IsNullOrEmpty(value) ? null : JsonConvert.DeserializeObject<Attendance>(value);
      }
  }
  

Quick Notes #

• Make sure you're using the latest version of SQLite-net (or SQLite-net-pcl) to avoid any outdated bugs with type mapping.
• The relational approach (join tables/foreign keys) is better if you need to query or filter based on individual items in the list or properties of Attendance. The JSON approach is faster to implement for simple use cases where you just need to store and retrieve the entire collection/object.

内容的提问来源于stack exchange，提问作者the_tr00per