Join in entity framework but fetch error.

Question

0.00/5 (No votes)

See more:

, +

I have two model first is expense and second is Itemmodels. Store all Product in Itemmodels with id,Name and in expense store only itemId. so i need get record from both table through join code is below. But this is fetch error:

LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression

C#

var data = db.expense
                    .Join(db.Itemmodels, q => q.ITEM, o => o.id, (q, ITEM) => new { q, ITEM })
                    .Where(o => o.q.EntryDate.ToString() == DateTime.Now.ToString("dd/MM/yyyy")).Where(o => o.q.Manager.ToString() == managerName).Where(o => o.q.THROUGH.ToString() != "CashTransfer")
                    .ToList();

Posted 20-Jun-16 23:31pm

Abhilask kumar

Updated 21-Jun-16 1:36am

Maciej Los

v2

Add a Solution

Comments

Maciej Los 21-Jun-16 5:53am

Date is date and nothing else! You shouldn't convert it into formated string!

Abhilask kumar 21-Jun-16 6:23am

But in model and database, EntryDate is string type.

2 solutions

Solution 1

Please, read my comment to the question.

This should work as well:

C#

var data = db.expense
                    .Join(db.Itemmodels, q => q.ITEM, o => o.id, (q, ITEM) => new { q, ITEM })
                    .Where(o => o.q.EntryDate == DateTime.Today && o.q.Manager == managerName && o.q.THROUGH != "CashTransfer")
                    .ToList();

Posted 20-Jun-16 23:54pm

Maciej Los

Updated 21-Jun-16 0:22am

v3

Comments

Karthik_Mahalingam 21-Jun-16 6:00am

Hi Maciej
EntryDate == DateTime.Today
the above condition will never match at all. since it will check the Timestamp as well.

Maciej Los 21-Jun-16 6:16am

It depends on data type of EntryDate field. If it's Date, not DateTime data type, the query should works as well. If not, you're right.
Thank you for your comment, Karthik.

Karthik_Mahalingam 21-Jun-16 6:25am

even though it is Date Type (in db) it will be having value as "06/21/2016 00:00:00" in EntryDate( c# property) but the DateTime.Now will be having the Timestamp like "06/21/2016 15:53:21"
so it will never match at all.
and sorry if i am wrong.

Maciej Los 21-Jun-16 6:27am

As you can see, i replaced DateTime.Now with DateTime.Today ;)

Karthik_Mahalingam 21-Jun-16 6:31am

now it should be fine with the Date issue.
5 for it.

Maciej Los 21-Jun-16 6:39am

Thank you, Karthik.

Abhilask kumar 21-Jun-16 6:22am

But in model and database, EntryDate is string type.

Maciej Los 21-Jun-16 6:24am

Well... Sounds like very BAD design!

Abhilask kumar 21-Jun-16 6:26am

when i use this db.expense.Where(p => p.EntryDate == DateTime.Now.ToString("dd/MM/yyyy")).Where(o => o.Manager == managerName).Where(o => o.THROUGH != "CashTransfer").Select(m => m.ITEM).ToList()
then work fine but when i use join then this error will happen.

Karthik_Mahalingam 21-Jun-16 6:26am

Change the EntryDate Column type to "Date"/"DateTime" in Db Table Schema.

Maciej Los 21-Jun-16 6:28am

Very good advice!

Abhilask kumar 21-Jun-16 6:34am

thank you for replay,
What can i do?
problem not in where condition.

Karthik_Mahalingam 21-Jun-16 6:50am

post the class code for expense and Itemmodels

Abhilask kumar 21-Jun-16 7:24am

public class Itemmodel
{
[Key]
public int id { get; set; }
[Required]
[MaxLength(50, ErrorMessage = "Cannot be longer than 50 characters.")]
[Display(Name="Item Name")]
public string ITEM { get; set; }
[Required]
[MaxLength(5, ErrorMessage = "Cannot be longer than 5 characters.")]
[Display(Name="Item Unit")]
public string UNIT { get; set; }
[Required]
public int STATUS { get; set; }
//public virtual ICollection<tbl_expense> tbl_expenses { get; set; }
}

public class expense
{
[Key]
public int id { get; set; }
[Display(Name="Location")]
public string Manager { get; set; }
[Required]
[Display(Name = "Item Name")]
public int ITEMID { get; set; }
[Required]
[Display(Name = "Date")]
[DataType(DataType.Date)]
public DateTime DATE { get; set; }
[Required]
[Display(Name="Through")]
[MaxLength(24, ErrorMessage = "Cannot be longer than 25 characters.")]
public string THROUGH { get; set; }
[Required]
[Display(Name="Quantity")]
public float QTY { get; set; }
[Required]
[Display(Name = "Amount")]
public float AMT { get; set; }
[Required]
[Display(Name="Remarks")]
public string REM { get; set; }
public string LOC { get; set; }
public string EntryDate { get; set; }
}

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Accepted Answer · 2016-06-21T01:36:00

Solution 2

try like this

C#

string currentDate = DateTime.Now.ToString("dd/MM/yyyy");
      var data = db.expense.Join(db.Itemmodels, exp => exp.ITEMID, im => im.id, (exp, im) => new { exp = exp, im = im })
                     .Where(o => o.exp.EntryDate == currentDate &&  o.exp.Manager == managerName &&  o.exp.THROUGH != "CashTransfer")
                     .ToList();

Posted 21-Jun-16 1:36am

Karthik_Mahalingam

Updated 21-Jun-16 2:11am

v2

Comments

Maciej Los 21-Jun-16 8:08am

Well... It meets OP requirements, although Where clause repeated 3 times looks ugly. ;(
Improve it, and you'll get 5 from me ;) Till now, a 4!

Karthik_Mahalingam 21-Jun-16 8:12am

ha ha good catch.
done sir :)
Thank you.

Maciej Los 21-Jun-16 8:14am

5ed!

Karthik_Mahalingam 21-Jun-16 8:18am

Thank you Maciej Los

Abhilask kumar 22-Jun-16 0:54am

Sir many many thank you.
and sir i'm new in mvc and entity framework.

Karthik_Mahalingam 22-Jun-16 1:21am

cool.

Abhilask kumar 22-Jun-16 1:56am

Sir one more help when this result transfer to view then this error has occur "The model item passed into the dictionary is of type 'System.Collections.Generic.List`1[<>f__AnonymousType3`2[Expense.Models.tbl_expense,Expense.Models.Itemmodel]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[Expense.Models.tbl_expense]'.
"

Karthik_Mahalingam 22-Jun-16 2:21am

convert the data to type tbl_expense