CODEWITHSUNDEEP
c#sqlasp.net-mvclinqentity-framework
Saroj
Saroj

Reputation: 526

Conversion of Sql query to linq

I am trying to convert sql query for select to linq query using EF in MVC but really got stuck with an error.

In SQL I'm able to get 6 records for my query,similarly when I try to convert this to linq it shows some error.

Following is my query in SQL:

SELECT        
   PurchaseOrderMaster.*, PurchaseOrderDetails.*, Vendor.*, 
   BusinessUnit.*, InvoiceMaster.*, TenantEmployee.*
FROM            
   PurchaseOrderMaster 
INNER JOIN
   PurchaseOrderDetails ON PurchaseOrderMaster.TenantID = PurchaseOrderDetails.TenantID 
                        AND PurchaseOrderMaster.PurchaseOrderNumber = PurchaseOrderDetails.PurchaseOrderNumber 
                        AND PurchaseOrderMaster.PurchaseOrderDate = PurchaseOrderDetails.PurchaseOrderDate 
INNER JOIN
   InvoiceMaster ON PurchaseOrderMaster.TenantID = InvoiceMaster.TenantID 
                 AND PurchaseOrderMaster.PurchaseOrderNumber = InvoiceMaster.PurchaseOrderNumber 
                 AND PurchaseOrderMaster.PurchaseOrderDate = InvoiceMaster.PurchaseOrderDate 
INNER JOIN
    BusinessUnit ON PurchaseOrderMaster.TenantID = BusinessUnit.TenantID 
                 AND PurchaseOrderMaster.BusinessUnitID = BusinessUnit.BusinessUnitID 
INNER JOIN
    TenantEmployee ON PurchaseOrderMaster.TenantID = TenantEmployee.TenantID 
INNER JOIN
    Vendor ON PurchaseOrderMaster.TenantID = Vendor.TenantID 
           AND PurchaseOrderMaster.VendorID = Vendor.VendorID

For this query I am able to get 6 records .

And my linq query is:

return (from pom in db.PurchaseOrderMaster
                    join pod in db.PurchaseOrderDetails on pom.TenantID equals pod.TenantID
                    where pom.PurchaseOrderNumber == pod.PurchaseOrderNumber && pom.PurchaseOrderDate == pod.PurchaseOrderDate
                    join inv in db.InvoiceMaster on pom.TenantID equals inv.TenantID
                    where pom.PurchaseOrderNumber == inv.PurchaseOrderNumber && pom.PurchaseOrderDate == inv.PurchaseOrderDate
                    join bu in db.BusinessUnit on pom.BusinessUnitID equals bu.BusinessUnitID
                    join te in db.TenantEmployee on pom.TenantID equals te.TenantID                
                    join v in db.Vendor on pom.TenantID equals v.TenantID
                    where pom.VendorID == v.VendorID
                    orderby pom.PurchaseOrderNumber ascending, pom.PurchaseOrderDate                   descending
                select new { pom, pod, inv, bu, te, v }).ToList();

At the time of debugging,following is the error that I'm getting:

{"Invalid column name 'invoiceMasterModel_TenantID'.\r\nInvalid column name 'invoiceMasterModel_PurchaseOrderNumber'.\r\nInvalid column name 'invoiceMasterModel_PurchaseOrderDate'.\r\nInvalid column name 'invoiceMasterModel_InvoiceNumber'.\r\nInvalid column name 'invoiceMasterModel_InvoiceDate'.\r\nInvalid column name 'tenantEmployeeModel_TenantID'.\r\nInvalid column name 'tenantEmployeeModel_EmployeeID'."}

Inside Invoice Table it is not able to find some of the columns and hence throwing the error according to me..

I tried with many possible ways but was unable to solve this.

Any ideas..?

Upvotes: 2

Views: 143

Answers (3)

Saroj
Saroj

Reputation: 526

Problem was with my Entity.

What I did is,I added my entity again and according to that I recreated models for the associated tables removing the earlier ones.

It solved my problem finally .

Upvotes: 2

Phani Vankadari
Phani Vankadari

Reputation: 79

I found this link Entity Framework 5 Invalid Column Name error related to somewhat similar problem.

Here also similar kind of error happened after the date time field. Check if your datetime field PurchaseOrderDate is nullable.

Upvotes: 1

Saket Kumar
Saket Kumar

Reputation: 4835

Many tools exist that can convert your sql queries to linq, in case you don't wanna write it urself. Try the following sites, works well in my case:

http://www.sqltolinq.com/

http://www.linqpad.net/

Upvotes: -1

Related Questions