Alnubani
Reputation: 11
The required column 'Id' was not present in the results of a 'FromSql' operation
i have Model Like this
public class Transactions
{
[Key]
public int Id { get; set;}
public Guid IDs { get; set; }
public string LOCATION_CODE { get; set; }
public string TERMINAL_ID { get; set; }
public string SHIFT_NO { get; set; }
public string RCPT_NUM { get; set; }
public string RCPT_DT { get; set; }
public string BUSINESS_DT { get; set; }
public string RCPT_TM { get; set; }
public string INV_AMT { get; set; }
public string TAX_AMT { get; set; }
public string RET_AMT { get; set; }
public string TRAN_STATUS { get; set; }
public string OP_CUR { get; set; }
public string BC_EXCH { get; set; }
public string DISCOUNT { get; set; }
}
DBContext Like this
public class DBContext : DbContext
{
public DBContext(DbContextOptions<DBContext> options)
: base(options)
{
}
public DbSet<Transactions> Transaction { get; set; } = null!;
}
Controller like this
[HttpGet]
public async Task<IActionResult> Get()
{
var studentList = await _dbcontext.Transaction.FromSqlRaw("Exec GetTransactionsInfo").ToListAsync();
var Tran = studentList;
return Ok(studentList);
}
Proc Like This
ALTER PROCEDURE [dbo].[GetTransactionsInfo]
AS
BEGIN
CREATE TABLE #tempTrans (
[tr_trans_id] [uniqueidentifier] NOT NULL,
[tr_num] [nvarchar](30) NOT NULL,
[tr_inv_type_id] [int] NULL,
[tr_type_id] [int] NOT NULL,
[tr_dob_id] [uniqueidentifier] NOT NULL,
[tr_cust_id] [uniqueidentifier] NULL,
[tr_branch_id] [uniqueidentifier] NULL,
[tr_status_id] [int] NOT NULL,
[tr_subtotal] [decimal](12, 6) NULL,
[tr_total] [decimal](12, 6) NOT NULL,
[tr_disc] [decimal](12, 6) NOT NULL,
[tr_total_disc] [decimal](12, 6) NULL,
[tr_date] [datetime] NOT NULL,
[tr_cur_id] [uniqueidentifier] NULL,
[tr_ws] [nvarchar](50) NULL,
[tr_store_id] [uniqueidentifier] NOT NULL,
[tr_total_qty] [decimal](12, 6) NULL,
[tr_ref_trans_id] [uniqueidentifier] NULL,
[tr_remarks] [nvarchar](1000) NULL,
[tr_cust_disc] [decimal](12, 6) NULL,
[tr_barcode] [nvarchar](50) NULL,
[tr_del_cust_id] [uniqueidentifier] NULL,
[tr_cust_num] [nvarchar](50) NULL,
[tr_cust_name] [nvarchar](250) NULL,
[tr_cust_phone] [nvarchar](100) NULL,
[tr_cust_mobile] [nvarchar](50) NULL,
[tr_frac] [decimal](19, 6) NULL,
[tr_InsertedBy] [uniqueidentifier] NULL,
[tr_UpdatedBy] [uniqueidentifier] NULL,
[tr_DeletedBy] [uniqueidentifier] NULL,
[tr_operation] [nvarchar](5) NULL,
[tr_moved] [int] NULL,
[tr_driver_id] [uniqueidentifier] NULL,
[tr_delivery_fee] [decimal](12, 6) NULL,
[tr_reserve_id] [uniqueidentifier] NULL,
[tr_total_cost] [decimal](19, 6) NULL,
[tr_insurance] [decimal](19, 6) NULL,
[tr_finished] [bit] NULL,
[tr_table_id] [uniqueidentifier] NULL,
[tr_order_num] [nvarchar](50) NULL,
[tr_income1] [decimal](19, 6) NULL,
[tr_refunded] [bit] NULL,
[tr_income2] [decimal](19, 6) NULL,
[tr_notes] [nvarchar](max) NULL,
[tr_po_id] [uniqueidentifier] NULL,
[tr_inv_num] [nvarchar](150) NULL,
[tr_hall] [nvarchar](250) NULL,
[tr_tmp_ins] [bit] NULL,
[tr_order_date] [datetime] NULL,
[tr_occupied_seats] [int] NULL,
[tr_waiter_id] [uniqueidentifier] NULL,
[tr_point_card_id] [uniqueidentifier] NULL,
[tr_points] [decimal](12, 6) NULL,
[tr_employee_id] [uniqueidentifier] NULL,
[tr_ins_ret] [decimal](19, 6) NULL,
[tr_ins_finished] [bit] NULL,
[tr_preparedby] [uniqueidentifier] NULL,
[tr_moved2] [int] NULL,
[tr_tbl_num] [nvarchar](50) NULL,
[tr_deleted_date] [datetime] NULL,
[tr_have_ins] [bit] NULL,
[tr_action] [nvarchar](50) NULL,
[record_insertion_date] [datetime] NULL,
[record_last_updated_date] [datetime] NULL,
[tr_cc_num] [nvarchar](50) NULL,
[tr_tax_inclusive] [bit] NULL,
[tr_income_tax] [decimal](19, 6) NULL,
[tr_discount_tax] [decimal](19, 6) NULL,
[tr_del_fee_tax] [decimal](19, 6) NULL,
[tr_item_tax] [decimal](19, 6) NULL,
[tr_address_id] [uniqueidentifier] NULL,
[tr_donation] [decimal](19, 6) NULL,
[tr_cc_confirm_date] [datetime] NULL,
[tr_payment_methods1] [nvarchar](max) NULL,
[tr_payment_methods2] [nvarchar](max) NULL,
[tr_tobacco_tax] [decimal](19, 6) NULL,
[tr_cancel_date] [datetime] NULL,
[tr_cance_reason] [nvarchar](max) NULL,
[tr_op] [int] NULL,
[tr_is_online_order] [bit] NULL,
[CateringTypeID] [int] NULL,
[BrandID] [int] NULL,
[tr_onlineorder_discount_percentage] [decimal](19, 6) NULL,
[tr_passed_invoice] [int] NULL,
[Invoice_hash] [nvarchar](max) NULL,
[tr_trans_id_sequential] [uniqueidentifier] NULL,
[CallerFinishDate] [datetime] NULL
)
insert into #tempTrans
select * from Transactions where tr_store_id='3956E009-45AF-445B-9F83-858551671400'
CREATE TABLE #tempTransItem(
[td_trans_item_id] [uniqueidentifier] NOT NULL,
[td_trans_id] [uniqueidentifier] NOT NULL,
[td_item_id] [uniqueidentifier] NOT NULL,
[td_item_uom_id] [uniqueidentifier] NOT NULL,
[td_qty] [decimal](12, 6) NOT NULL,
[td_disc] [decimal](12, 6) NULL,
[td_price] [decimal](12, 6) NOT NULL,
[td_new_price] [decimal](12, 6) NULL,
[td_tax] [decimal](12, 6) NULL,
[td_total] [decimal](12, 6) NOT NULL,
[td_subtotal] [decimal](12, 6) NOT NULL,
[td_sku] [nvarchar](50) NULL,
[td_returned_qty] [decimal](12, 6) NULL,
[td_order] [bigint] NULL,
[td_cost] [decimal](12, 6) NULL,
[td_remarks] [nvarchar](1000) NULL,
[td_insurance] [decimal](19, 6) NULL,
[td_finished] [bit] NULL,
[td_print] [int] NULL,
[td_row_ind] [bigint] NULL,
[td_raw_finished] [bit] NULL,
[td_order_qty] [decimal](19, 6) NULL,
[td_prepare_period] [int] NULL,
[td_kit] [bit] NULL,
[td_equiv] [decimal](12, 6) NULL,
[td_tax_prcnt] [decimal](19, 6) NULL,
[td_order_ins_date] [datetime] NULL,
[td_item_cost] [decimal](19, 6) NULL,
[BaseItemUOMID] [uniqueidentifier] NULL,
[PromoID] [uniqueidentifier] NULL,
[Equiv] [decimal](12, 6) NULL
)
insert into #tempTransItem
select [td_trans_item_id],
[td_trans_id] ,
[td_item_id] ,
[td_item_uom_id],
[td_qty],
[td_disc] ,
[td_price] ,
[td_new_price],
[td_tax],
[td_total] ,
[td_subtotal] ,
[td_sku] ,
[td_returned_qty] ,
[td_order] ,
[td_cost],
[td_remarks] ,
[td_insurance] ,
[td_finished] ,
[td_print] ,
[td_row_ind] ,
[td_raw_finished] ,
[td_order_qty] ,
[td_prepare_period] ,
[td_kit] ,
[td_equiv],
[td_tax_prcnt] ,
[td_order_ins_date] ,
[td_item_cost] ,
[BaseItemUOMID],
[PromoID] ,null
--[Equiv]
from TransItem
inner join #tempTrans ON TransItem.td_trans_id=#tempTrans.tr_trans_id
CREATE TABLE #tempTransPayment
(
[tp_payment_id] [uniqueidentifier] NOT NULL,
[tp_trans_id] [uniqueidentifier] NOT NULL,
[tp_pay_meth_id] [uniqueidentifier] NOT NULL,
[tp_value] [decimal](18, 4) NOT NULL,
[tp_currency_id] [uniqueidentifier] NULL,
[tp_comm_value] [decimal](18, 10) NULL,
[tp_InsertedBy] [uniqueidentifier] NULL,
[tp_cust_card_id] [uniqueidentifier] NULL,
[tp_point_card_id] [uniqueidentifier] NULL,
[tp_points] [decimal](19, 6) NULL,
[tp_ins_val] [decimal](19, 6) NULL,
[tp_free_type_id] [uniqueidentifier] NULL,
[tp_insert_date] [datetime] NULL,
[record_insertion_date] [datetime] NULL,
[tp_id] [nvarchar](100) NULL,
[tp_emp_id] [uniqueidentifier] NULL,
[tp_stc_ref_num] [nvarchar](50) NULL,
[tp_stcpay_num] [nvarchar](50) NULL,
[tp_merchantid] [nvarchar](100) NULL,
[tp_authcode] [nvarchar](100) NULL,
[tp_schemeid] [nvarchar](100) NULL,
[tp_transRRN] [nvarchar](100) NULL,
[tp_stanno] [nvarchar](100) NULL
)
insert into #tempTransPayment
select
[tp_payment_id]
,[tp_trans_id]
,[tp_pay_meth_id]
,[tp_value]
,[tp_currency_id]
,[tp_comm_value]
,[tp_InsertedBy]
,[tp_cust_card_id]
,[tp_point_card_id]
,[tp_points]
,[tp_ins_val]
,[tp_free_type_id]
,[tp_insert_date]
,TransPayment.[record_insertion_date]
,[tp_id]
,[tp_emp_id]
,[tp_stcpay_num]
,[tp_stc_ref_num]
,[tp_merchantid]
,[tp_authcode]
,[tp_schemeid]
,[tp_transRRN]
,[tp_stanno]
from TransPayment
inner join #tempTrans on tp_trans_id=#tempTrans.tr_trans_id
DECLARE @Json NVARCHAR(MAX) =
(SELECT
JSON_QUERY((
ISNULL((
select top(1)
ROW_NUMBER() OVER(ORDER BY [tr_trans_id]) as Id,
[tr_trans_id] as IDs
,[sr_store_num] as LOCATION_CODE
,[tr_ws] as TERMINAL_ID
,'01' as SHIFT_NO
,[tr_num] as RCPT_NUM
,(FORMAT([tr_date],'yyyy-mm-dd') )as RCPT_DT
,(FORMAT([db_start_of_day],'yyyy-mm-dd')) as BUSINESS_DT
,(FORMAT([tr_date],'hh:mm:ss')) as RCPT_TM
,[tr_total] as INV_AMT
,[tr_income2] as TAX_AMT
,'0' as RET_AMT
,CASE
WHEN tr_type_id=0 THEN 'SALES'
ELSE 'RETURN'
END as TRAN_STATUS
,'SR' as OP_CUR
,1 as BC_EXCH
,[tr_disc] as DISCOUNT
, ItemDetail = isNULL((
SELECT
[tr_trans_id] as IDs
,[sr_store_num] as LOCATION_CODE
,[tr_ws] as TERMINAL_ID
,'01' as SHIFT_NO
,[tr_num] as RCPT_NUM
,[it_num] as ITEM_CODE
,[it_name1] as ITEM_NAME
,[td_qty] as ITEM_QTY
,[td_price] as ITEM_PRICE
,[vg_name1] as ITEM_CAT
,[td_tax] as ITEM_TAX
,CASE WHEN tr_tax_inclusive=0 THEN 'E'
ELSE 'I'
END as ITEM_TAX_TYPE
,[td_total] as ITEM_NET_AMT
,'SR' as OP_CUR
,1 as BC_EXCH
,CASE
WHEN tr_type_id=0 THEN 'SALES'
ELSE 'RETURN'
END as ITEM_STATUS
FROM #tempTransItem ti
inner join item on it_item_id=ti.td_item_id
left outer join ItemViewGroup on iv_item_id=it_item_id
left outer join ViewGroup on vg_view_group_id=iv_view_group_id
WHERE ti.td_trans_id = tr.tr_trans_id
FOR JSON PATH ),'[]'),
PaymentDetail = ISNULL((
SELECT
[tr_trans_id] as IDs
,[sr_store_num] as LOCATION_CODE
,[tr_ws] as TERMINAL_ID
,'01' as SHIFT_NO
,[tr_num] as RCPT_NUM
,[pm_name1] as PAYMENT_NAME
,'SR' as CURRENCY_CODE
,1 as EXCHANGE_RATE
,tp_value as TENDER_AMOUNT
,'SR' as OP_CUR
,1 as BC_EXCH
,CASE
WHEN tr_type_id=0 THEN 'SALES'
ELSE 'RETURN'
END as PAYMENT_STATUS
FROM #tempTransPayment tp
inner join PaymentMethods on pm_pay_meth_id=tp_pay_meth_id
WHERE tp.tp_trans_id = tr.tr_trans_id
FOR JSON PATH ),'[]')
from #tempTrans tr
inner join #tempTransItem on td_trans_id=tr_trans_id
inner join #tempTransPayment on tp_trans_id=tr_trans_id
inner join Store on tr.tr_store_id=sr_store_id
inner join DayOfBusiness on tr.tr_dob_id=db_dob_id
FOR JSON PATH, INCLUDE_NULL_VALUES),'[]')
)) AS [Transactions]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);
DROP TABLE #tempTrans
DROP TABLE #tempTransItem
DROP TABLE #tempTransPayment
select @Json as [data]
END
I tired of trying without any result the same error every time and It works fine when I run in query console. But on the browser I am getting this error: The required column 'Id' was not present in the results of a 'FromSql' operation.
Upvotes: 1
Views: 255
Answers (1)
Charlieface
Reputation: 72229
You have two issues here, both centred on the fact that your SQL is already JSON-ifying your data:
FromResultexpects a resultset, but you are just returning a single JSON string of all the results.Ok(studentList)isn't going to work either because it will try to double-escape the JSON.
Instead you need to execute a custom SqlCommand, and use return Content(....
[HttpGet]
public async Task<IActionResult> Get()
{
using var command = new SqlCommand("GetTransactionsInfo", (SqlConnection)_dbContext.Database.GetDbConnection());
command.CommandType = CommandType.StoredProcedure;
// add parameters if any:
// command.Parameters.Add("@someParam", SqlDbType.UniqueIdentifier).Value = someValue;
var studentListJson = (string)await command.ExecuteScalarAsync();
return Content(studentListJson, "application/json");
}
Upvotes: 0
Related Questions
- How do I remedy "The breakpoint will not currently be hit. No symbols have been loaded for this document." warning?
- Could not find a part of the path ... bin\roslyn\csc.exe
- A potentially dangerous Request.Form value was detected from the client
- The required column 'id' was not present in the results of a `FromSql` operation
- Gmail Error :The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required
- C# Entity Framework generates a table column not in my model
- How to verify that method was NOT called in Moq?
- The required column 'CustomerId' was not present in the results of a 'FromSql' operation
- "Member AutoSync Failure" using TPT, view and instead of insert trigger