insert from linq to sql with join

Question

I have the following query

var listOfFollowers = (from a in db.UserLinks
        where a.TargetUserID == TargetUserID && a.LinkStatusTypeID == 2 
        join b in db.UserNotifications on (int)a.OriginUserID equals b.TargetUserID
        select b);

then I want to update once column on each row ( or object) returned

 foreach (var a in listOfFollowers)
    {
      a.UserNotifications += HappeningID.ToString() + "|";
    }
    db.SubmitChanges();

The query seems to work , and when I put the generated SQL into SSMS it works fine , but when I run the entire code I get exception for trying to cast to int, don't make too much sense.

Is this ok , to do a query using a join , but only returning one table , change one property, then submitchanges?

try this var listOfFollowers = (from a in db.UserLinks where a.TargetUserID == TargetUserID && a.LinkStatusTypeID == 2 join b in db.UserNotifications on a.OriginUserID equals SqlFunctions.StringConvert((double)b.TargetUserID) select b); — Shafqat Masood, Apr 25 '13 at 20:54

score 2 · Accepted Answer · edited May 23 '17 at 11:57

2

The reason you are getting can't cast exception is that in the LINQ statement it is invalid to do cast. Other things which we can't do is to use regular method calls such as toString()

(int)a.OriginUserID is not allowed in

   var listOfFollowers = (from a in db.UserLinks
    where a.TargetUserID == TargetUserID && a.LinkStatusTypeID == 2 
    join b in db.UserNotifications on (int)a.OriginUserID equals b.TargetUserID
    select b);

This problem will occur because parser tries to convert it into a equivalent SQL but doesn't find any equivalent. Other such cases are when you try to invoke toString(). Several Good responses here: linq-to-entities-does-not-recognize-the-method

For your current scenario, I believe you have to 1. get the results from first query 2. cast the value 3. Make the second LINQ query

Hope this helps!

edited May 23 '17 at 11:57

Community

1
1

answered Apr 25 '13 at 20:54

prashantsunkari

959
7
21

I didn't use ToString() , seems that would be ineffecient to compare two numbers, but it was the casting to an int that was throwing exception , I changed db to make both columns bigint , instead of one int and one bigint – Scott Selby Apr 25 '13 at 21:48
You were right about the improper cast, so you got my accept and upvote but doing one query , then casting , then another query is from form the most efficient way to do this. – Scott Selby Apr 25 '13 at 21:51
@Scott. Cast was definitely the issue in your case. I meant to say there are other things which we can't do in LINQ statement such as using methods such as "toString()". I have edited my answer a little to make it clear. – prashantsunkari Apr 25 '13 at 21:52

score 0 · Answer 2 · answered Apr 25 '13 at 20:54

0

Most likely, problem occures in this piece of code (int)a.OriginUserID.
Try remove casting or use SqlFunctions.StringConvert() to compare strings.

answered Apr 25 '13 at 20:54

Dima

6,721
4
24
43

insert from linq to sql with join

2 Answers2