Q: The problem with getting data from a database with tables with the same names I have a database with many tables. To execute a single data retrieval query, I use a raw sql query with joins of several tables.
And the problem is that some tables have fields with the same names and without assigning a new name to these fields during sql query execution, mapping behavior is not predictable. It is not clear which object will be assigned a field from the database if there is a field with the same name. Here is an example of the code I'm using at the moment:
var response = await _db.rawQuery('''
        select Products.Id,
        Products.Name as ProductName,
        Products.Description,
        Products.Article,
        Products.Barcode,
        Products.Country,
        Prices.PriceValue,
        MainUnits.Name as MainUnitsName,
        StockBalances.Count,
        WareHouses.Name as WareHousesName,
        WareHouses.Address,
        WareHouses.Telephone,
        PriceTypes.Name as PriceTypesName
        from Products
        inner join Prices
        on Products.Id = Prices.ProductId
        inner join StockBalances
        on Products.Id = StockBalances.ProductId
        inner join WareHouses
        on StockBalances.WareHouseId = WareHouses.Id
        inner join PriceTypes
        on Prices.PriceTypeId = PriceTypes.Id
        inner join MainUnits
        on Prices.MainUnitId = MainUnits.Id
        where Prices.PriceTypeId = 1 and MainUnits.IsGeneral = 1
        '''
    );

I would like to learn more best practices of working with sql, preferably with minimal use of pure sql, otherwise it seems to me that the way I use it is not quite correct and not convenient. I was hoping to start using Select * from... to somehow shorten this sql query, but this is impossible since the fields have the same names, and I have to set a new name using as.

A: First of all, never use "Select * " - as you have no control of the number/order of fields - always use the explicit names.
Make your select as small as possible, as the number of fields and their length can impact performance and data transfeer.
Using ORMS might help you, like:
https://github.com/DapperLib/Dapper
https://learn.microsoft.com/en-us/ef/
https://www.tutorialspoint.com/sqlite/sqlite_alias_syntax.htm
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];
