How to use Is null or empty in raw sql for assigned value which is not in table?

Question

How to check IsNullOrEmpty in RawSQL .If control number and sender Id both have values,then check like condition for both.If it having sender id alone check like condition for sender id alone and If it having controlnumber alone check like condition for control number alone. It shows error like

Incorrect syntax near *

using (var context = new BSoftWEDIIContext())
                {

                    if (!string.IsNullOrEmpty(controlNumber))
                    {
                        controlNumber = "*" + controlNumber + "*";
                    }
                    if (!string.IsNullOrEmpty(senderNumber))
                    {
                        senderNumber = "*" + senderNumber + "*";
                    }

                    var fileDetail = context.FileDetails
                        .SqlQuery("select * from FileDetails where @" + controlNumber.ToString()
                        +" is not null OR CONVERT(varchar(max), RawData) like '%" + controlNumber.ToString() 
                        + "%' AND CONVERT(varchar(max), RawData) like '%" + senderNumber.ToString()+"%'").ToList();
                    matchedFileId = fileDetail?.Select(a => a.Id).ToList();

                }

Answer 1

You are appending too many * and the resulting query is not building properly. Check for the condition accordingly:

using (var context = new BSoftWEDIIContext())
            {

                if (!string.IsNullOrEmpty(controlNumber))
                {
                    controlNumber = controlNumber;
                }
                if (!string.IsNullOrEmpty(senderNumber))
                {
                    senderNumber = senderNumber;
                }

                var fileDetail = context.FileDetails.SqlQuery("select * from FileDetails where " + controlNumber.ToString() + " is not null" + " OR CONVERT(varchar(max), RawData) like '%" + controlNumber.ToString() + "%' AND CONVERT(varchar(max), RawData) like '%" + senderNumber.ToString() + "%'").ToList();
                matchedFileId = fileDetail?.Select(a => a.Id).ToList();
            }

---

Note: This approach is prone to SQL Injection and is not recommended at all. Please read about parameterised query and best practices from below links:

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/how-to-execute-a-parameterized-entity-sql-query-using-entitycommand

how to change sql statement to parameterized query?