Accessing a single field in a foreach using Linq Dapper

Question

I am using dapper and I want to use Linq to be able to update a single field called status in one table I am trying to use.

public async Task<Int32> ProcessUnprocessedTransactions(IEnumerable<BomComponentData> items)
{
    IEnumerable<BomComponentData> _itemstoBeProcesed = items.Where(w => w.Status == (int)BomStatus.Scanned);

    foreach (BomComponentData item in _itemstoBeProcesed)
    {
        item.Status = (int)BomStatus.Completed;
    }            

    return await database.UpdateAsync(_itemstoBeProcesed);       
}

My class is as follows:

public class BomComponentData
{
    public int Sequence { get; set; }

    public string BOMShortName { get; set; }
    public string OperationName { get; set; }
    public long BomId { get; set; }
    public long BOMLineID { get; set; }
    public long StockItemID { get; set; }
    public string BomLineType { get; set; }
    public int Quantity { get; set; }
    public long UnitID { get; set; }
    public decimal? MultipleOfBaseUnit { get; set; }
    public string Code { get; set; }
    public string Name { get; set; }
    public string Barcode { get; set; }
    public long ProductGroupID { get; set; }
    public string ProductGroupCode { get; set; }
    public int Status { get; set; }

    public int BinLocation { get; set; }
    public string BinName { get; set; }

    public string UOM { get; set; }
    public int CalculatedValue { get; set; }
    public int BomPickedCount { get; set; }

    public int TotalLeftTopick
    {
        get { return Quantity - BomPickedCount; }         
    }

    public enum BomStatus
    {
        Listed=1,
        Scanned=2,
        Confirmed=3,
        Processed=4,
        Completed=4,
        InVisible=5
    }

    public override string ToString()
    {
        return Code; 
    }
}

But it does not work if I use a foreach like above. I am sure it should update the items properly but I think that because I'm going through singular items in my foreach and my list in the update it's not updating correct.

All I want to do is mark the items as completed and ready for transfer, I am doing so by the status column and an int enum.

Maybe I am missing a declaration of what is my primary key?

Edit 2

When I use a key declaration of the primary key I get the following:

Unhandled Exception: System.AggregateException: One or more errors occurred. (Constraint

Edit 3

I have set key of my class but as you see I have auotincrement on my db and it still crashes. How should insert be handled?

Edit 4

For example I am inserting into the database as follows. Shouldn't this work?

List<BomComponentData> _bomList = new List<BomComponentData>();
_bomList.Add(new BomComponentData { Sequence = 1, Barcode = "0000000001498", BinLocation = 23, BinName = "A", BOMShortName = "GYNE-TXX", OperationName = "Example Product", Code = "TB9175CEA", Name = "Tiburon Peri/Gynae Pack-10", Quantity = 1, UOM = "Each" });


await database.InsertAllAsync(_bomList,true);

I have placed the tag key for the update that works ok but when I attempt to do an insert with the key it doesn't it says constraint error but the update works. Does anybody no how i can solve both the insert and update in Dapper contrib.

Answer 1

You are using Dapper.Contrib and this extension requires that you decorate your class with some attributes to help in the automatic handling of your data.

In particular for an Update method you need to define the Table attribute and the Key attribute to identify the primary key

[Table ("BomComps")]
public class BomComponentData
{
   // [ExplictKey]
   [Key]
   public int Sequence { get; set; }
   ....

Here, for example, I have added the attribute Table to set a possible table name on the database, but if the name of the physical table matches the name of the class then you can omit the attribute. Also I have added the Key attribute to set the property that contains the primary key of your table (so the statement that updates your records could be formed with the proper WHERE condition).

Notice that the Key attribute should be used if the column has an Identity property set to yes while, if not, you use the ExplicitKey attribute to signal that the primary key is defined by your code.

Answer 2

This was actually the issue I had to decoate my class with the following Leaving this here so that anyone else has issue I was using the pcl libary but for some reason dapper contribe did not detect the key element it had to be declared as follows.

[SQLite.PrimaryKey, SQLite.AutoIncrement]
public class StockTransferArgs
{
    [SQLite.PrimaryKey, SQLite.AutoIncrement]
    public int StockTransferArgsId { get; set; }
    public long StockItemID { get; set; }

    public string Code { get; set; }

    public string OperationName { get; set; }
    public string Description { get; set; }
    public decimal Quantity { get; set; }
    public long SourceWarehouseID { get; set; }
    public string SourceBinName { get; set; }
    public long TargetWarehouseID { get; set; }
    public string TargetBinName { get; set; }
    public string Reference { get; set; }
    public string SecondReference { get; set; }
    public string BarCode { get; set; }
    public int Status { get; set; }
}