Conversion of Sql query to linq

Question

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..?

Have you refresh your dbml file???? because if you add new columns to your database you need to do this or delete content of dbml file and add those tables again!!! have you tried this???? — Rudra Sisodia, Apr 29 '14 at 07:42
@RudraSisodia,its refreshed,I'm having all the columns in that table inside my entity.Still it is not able to find those columns. — Saroj, Apr 29 '14 at 07:53

score 2 · Accepted Answer · answered May 02 '14 at 06:27

2

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 .

answered May 02 '14 at 06:27

Saroj

526
1
5
17

score 1 · Answer 2 · edited May 23 '17 at 12:28

1

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.

edited May 23 '17 at 12:28

Community

1
1

answered Apr 29 '14 at 08:52

Phani Vankadari

79
3

score -1 · Answer 3 · answered Apr 29 '14 at 07:44

-1

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/

answered Apr 29 '14 at 07:44

Saket Kumar

4,363
4
32
55

I don't suggest any tool it is decrease the performance except paid tool – Amit Apr 29 '14 at 07:48
Ya,I dont want to use any conversion tool.I've already written few queries which I first wrote in sql and then later in linq ,it worked fine for me but here it is showing this error. – Saroj Apr 29 '14 at 07:57

Conversion of Sql query to linq

3 Answers3