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.

You decorate the class with the proper attribute [Key] for Identity or [ExplicitKey] for primary key defined by your code. Notice that you are using a Dapper extension. See here for info: https://github.com/StackExchange/Dapper/tree/master/Dapper.Contrib — Steve, May 26 '19 at 20:14
This scenario seems to scream "do it all at the database in pure SQL"... fetching everything over the wire just to send it back as updates seems like doing it the hard way, no? — Marc Gravell, May 26 '19 at 23:33
I have to application im calling is wcf and its single threaded in nature so i have no other way but to do it on the device and then send to the wcf. — c-sharp-and-swiftui-devni, May 26 '19 at 23:51

Steve · Answer 1 · 2019-05-26T21:31:12.200

2

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.

edited May 26 '19 at 21:31

answered May 26 '19 at 20:27

Steve

213,761
22
232
286

please see my edit above i am using xamrian forms and get an exception after applying key. – c-sharp-and-swiftui-devni May 26 '19 at 21:35
So this code runs inside a mobile application on a mobile device? And you are updating a database on this device? What database are you using? In the Dapper.Contrib docs there is a warning about SQLite conflicts with its native Update method – Steve May 26 '19 at 21:47
thanks steve and yes you are correct sql lite and sorry I did not no this. – c-sharp-and-swiftui-devni May 26 '19 at 21:54
Is there a workaround as dapper contribute is the best so it is – c-sharp-and-swiftui-devni May 26 '19 at 22:41
I have my database table set to autoincrment but still same situation occuring. – c-sharp-and-swiftui-devni May 26 '19 at 22:49
Please see my edits above I really love Dapper contrib hope can use with sqllite. – c-sharp-and-swiftui-devni May 26 '19 at 23:06
Sorry, but I don't know what's happening. I would add the proper tag to your question to attract the interest of some people very expert on dapper and sqlite – Steve May 27 '19 at 06:11

score 0 · Answer 2 · answered May 27 '19 at 14:30

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; }
}

Accessing a single field in a foreach using Linq Dapper

2 Answers2