How to group based on item , supplier, unit. And getting the latest record in every group

See more:

C#

SQL

MySQL

EF6.0

I need to get the latest purchases cost for every group based on Item , supplier, unit. and synchronization it to another location ..

item 1, pack, Jone, ...
item 1, pack, Mich, ...
item 1, pack, Yasser, ...
item 2, pack, Jone, ...
item 2, pack, Mich, ...
item 3, pack, Yasser, ...

with some conditions:
 u.cost != u.syns_cost
u.Unit != u.syns_unit
 u.pmain_Date >= last30DaysDate

i tried to created Full MySQL view but i failed

What I have tried:

I created MySQL View:

CREATE VIEW purchases_data_view AS
SELECT 
    i.stitems_ID AS item_id,
    i.stitems_Name AS item_name,
    i.stitems_Code AS general_code,
    i.stitems_JeptusCode AS vatoce_code,
    i.stitems_Pharma_ActiveIngredient as main_component,
    f.manu_Name AS manufacturer_name,
    cat.stcate_Name AS category_name,
    pi.item_NetUnitPrice AS cost,
    uns.Unit,
    syns.syns_cost,
    syns.syns_unit,
	pm.pmain_Date,
	pm.vendor_ID as supplier_id,
    p.vatoce_code as supplier_vatoce_code,
    p.pepole_Name as supplier_name,
    p.pepole_City as supplier_city,
    p.pepole_Address as supllier_address,
    p.pepole_Mobile as supplier_phone,
  sunits.Unit as small_unit_name,
  sunits.stitemsu_PurchasesPrice as small_unit_purchase_price,
  sunits.stitemsu_SalesPrice as small_unit_sales_price,
  lunits.Unit as large_unit_name,
  lunits.stitemsu_UnitNum as unit_count,
  lunits.stitemsu_PurchasesPrice as large_unit_purchase_price, 
  lunits.stitemsu_SalesPrice as large_unit_sales_price,
  lunits.stitemsu_SalesPrice as audience_price
FROM
    st_items i

INNER JOIN (SELECT    
				MAX(pr_item.pitem_ID) as pitem_ID, 
				pr_item.pmain_ID, 
				pr_item.stitems_ID, 
				pr_item.unit_ID,
				pr_item.item_NetUnitPrice
	  FROM purchases_item pr_item
	  GROUP BY 
				pr_item.pmain_ID, 
				pr_item.stitems_ID, 
				pr_item.unit_ID,
				pr_item.item_NetUnitPrice) pi
	ON i.stitems_ID = pi.stitems_ID

JOIN (SELECT 
				pr_main.pmain_ID,
				pr_main.pmain_Date,
				pr_main.vendor_ID
				FROM purchases_main pr_main
				where pr_main.vendor_ID NOT IN (1, 2, 3)
			 ) pm
		on pi.pmain_ID = pm.pmain_ID
        
JOIN (SELECT 
		units.stitemsu_ID,
		units.stitemsu_UnitName,
        units.stitemsu_UnitNum,
        units.stitemsu_SalesPrice,
        un.Unit
		FROM st_items_units units
					JOIN (SELECT 
					unname.ID,
					unname.Unit
					FROM st_units_name unname
				 ) un
			on un.ID = units.stitemsu_UnitName
	 ) uns
on pi.unit_ID = uns.stitemsu_ID

 LEFT JOIN (SELECT 
							  sunit.stitems_ID,
							  sunit.UnitDetID,
                              sunit.Unit,
                              sunit.stitemsu_PurchasesPrice,
                              sunit.stitemsu_SalesPrice,
							  sunit.stitemsu_SalesDiscount
								  FROM view_units_1stunit sunit
								  GROUP BY sunit.stitems_ID,sunit.UnitDetID,sunit.Unit,sunit.stitemsu_PurchasesPrice,sunit.stitemsu_SalesPrice, sunit.stitemsu_UnitNum, sunit.stitemsu_SalesDiscount) sunits
					   ON sunits.stitems_ID = i.stitems_ID
                       
                       INNER JOIN (SELECT 
							  lunit.stitems_ID,
                              lunit.UnitDetID,
							  lunit.Unit,
                              lunit.stitemsu_PurchasesPrice,
                              lunit.stitemsu_SalesPrice,
                              lunit.stitemsu_UnitNum ,
							  lunit.stitemsu_SalesDiscount
								  FROM view_units_lastunit lunit
								  GROUP BY lunit.stitems_ID,lunit.UnitDetID,lunit.Unit,lunit.stitemsu_PurchasesPrice,lunit.stitemsu_SalesPrice, lunit.stitemsu_UnitNum , lunit.stitemsu_SalesDiscount) lunits
					   ON lunits.stitems_ID = i.stitems_ID

LEFT JOIN
    st_categories cat ON i.stcate_ID = cat.stcate_ID
LEFT JOIN
    st_plug_manufacturer f ON i.stitems_Manufacturer = f.manu_ID
    
LEFT JOIN
    people_data p ON pm.vendor_ID = p.pepole_ID
    
LEFT JOIN (SELECT 
			MAX(sns.id) as id,
				sns.stitems_ID,
				sns.pepole_ID,
                sns.last_price as syns_cost,
                sns.unit as syns_unit
				FROM suppliers_prices_syns sns
				group by sns.id, sns.stitems_ID, sns.pepole_ID  order by sns.id desc) syns
		 ON i.stitems_ID = syns.stitems_ID and pm.vendor_ID = syns.pepole_ID and  uns.Unit = syns.syns_unit

also EF6 Query :

var DB1 = ConnectionTools.OpenConn();
            var virusalPeople = new List<int> { 1, 2, 3 };

            var last30DaysDate = DateTime.Now.AddDays(-3000);

var query = DB1.purchases_data_view
                        .Where(u => u.cost != u.syns_cost &&
                u.Unit != u.syns_unit &&
                !virusalPeople.Contains(u.supplier_id.Value) &&
                u.pmain_Date >= last30DaysDate
                && u.item_id == 14989)
                    .GroupBy(u => new
                    {
                        u.item_id,
                        u.Unit,
                        u.supplier_id,
                    })
                    .Select(g => g.OrderByDescending(u => u.pmain_Date).FirstOrDefault())
                    .Take(100)
                    .ToList();




                var resultAsString = string.Join(Environment.NewLine, query.Select(r => $"{r.supplier_id}"));

                Console.WriteLine(resultAsString);

testing that with item id =

14989

the result (not expected it should one record only per group but here many repeated data):

14989, قطعة, 76
14989, قطعة, 12
14989, قطعة, 12
14989, قطعة, 119
14989, قطعة, 119
14989, قطعة, 76
14989, قطعة, 76
14989, قطعة, 67
14989, قطعة, 273
14989, قطعة, 67
14989, قطعة, 76
14989, قطعة, 76
14989, قطعة, 67
14989, قطعة, 67
14989, قطعة, 67
14989, قطعة, 113
14989, قطعة, 113
14989, قطعة, 67
14989, قطعة, 67
14989, قطعة, 113
14989, قطعة, 67
14989, قطعة, 414
14989, قطعة, 67
14989, قطعة, 67
14989, قطعة, 414
14989, قطعة, 414
14989, قطعة, 67