Reference for learning basic LINQ for SQL? C# Expressions or C# Statements

Zukoh

Is there a simple reference/cheat sheet?

I'm familiar with C++ and some SQL.
I don't know why I should use Expressions, or Statements, or Entities and am mixing up the syntax so its confusing.
I just want to learn what I think should be simple things like:

SQL:
from p in ProviderInfo join r in TM99R.RM00101s on p.PHONE equals r.PHONE1 and r.INACTIVE = 0


LINQ does not have an AND so I tried
new{ p.PHONE ,r.INACTIVE } equals new{ r.PHONE1 ,1 }

but it complains: "CS0746 Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access."

sgmoore

The compile error you are getting is because you have to name each member in the anonymous type and hence can't just use a constant.

Also, in order to compare the anonymous types, they must contain the same names and types. so you would need something like (assuming Inactive is an int)

new { p.PHONE ,r.INACTIVE } equals new { PHONE = r.PHONE1 ,INACTIVE = 1 }

If it is a bool, then you would need
new { p.PHONE ,r.INACTIVE } equals new { PHONE = r.PHONE1 ,INACTIVE = true }

or if it is nullable you would need
new { p.PHONE ,r.INACTIVE } equals new { PHONE = r.PHONE1 ,INACTIVE = (bool?) true }

Zukoh

When I try
new{ p.PHONE, r.INACTIVE } equals new{ PHONE = r.PHONE1 ,INACTIVE = 1 }
I get CS1937 The name 'r' is not in scope on the left side of 'equals'. Consider swapping the expressions on either side of 'equals'

So I try new{ PHONE1 = p.PHONE ,INACTIVE = 1 } equals new{ r.PHONE1 ,r.INACTIVE }
I get CS1941 The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'

Here is the code in its entirety:
var custs = new[] { "ACT03" ,"MAS03" }; var matches = ( from p in ProviderInfo join r in TM99R.RM00101s on //p.PHONE equals r.PHONE1 // CS1937 The name 'r' is not in scope on the left side of 'equals'. Consider swapping the expressions on either side of 'equals' //new{ p.PHONE, r.INACTIVE } equals new{ PHONE = r.PHONE1 ,INACTIVE = 1 } // CS1941 The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join' new{ PHONE1 = p.PHONE ,INACTIVE = 1 } equals new{ r.PHONE1 ,r.INACTIVE } //where custs.Contains( r.CUSTNMBR ) select new { p.Provnum ,p.PROVNAME ,p.PHONE ,r.CUSTNMBR ,r.USERDEF1 ,r.USERDEF2 ,r.FRSTINDT ,r.INACTIVE ,r.HOLD } ); foreach( var c in matches ) { Console.Write( c ); }

Really appreciate the help!

sgmoore

That means one of the types are different and is most likely that
new { PHONE1 = p.PHONE ,INACTIVE = 1 } is defined as string, int
whereas new{ r.PHONE1 ,r.INACTIVE } is defined as string, bool, or string, bool?

So you need to make your constant either bool or bool?, ie
new { PHONE1 = p.PHONE ,INACTIVE = false } or
new { PHONE1 = p.PHONE ,INACTIVE = (bool?) false }

PS.
Sorry about the errors in my previous answer.

Ytrog

A good though somewhat old site for LINQ examples is: https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

A thing to keep in mind with LINQ to SQL and EF is that expressions need to be translatable to SQL. So things like a new non-anonymous object in a select statement won't work (anonymous objects just get translated to columns in the SQL query)

Zukoh

@sgmoore no need to apologize, I'm very grateful for any help I can get

Ok, after looking at the microsoft site's overly complicated documentation on the "Equals Operator":https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/join-clause

I figured out a way that works. Basically, instead cramming the INACTIVE = 0 in the equals operation, do a subset of customers that are active first,
var activeCusts = from ar in TM99R.RM00101s where ar.INACTIVE == 0 select ar;
then join on that.

This actually makes sense and is cleaner than SQL. Just wish there was documentation that was less convoluted haha

Zukoh

Found a more flexible approach:

from p in ProviderInfo from r in TM99R.RM00101s.Where( r => ( p.PHONE.Substring( 0 ,10 ) == r.PHONE1.Substring( 0 ,10 ) || p.PHONE.Substring( 0 ,10 ) == r.PHONE2.Substring( 0 ,10 ) || p.PHONE.Substring( 0 ,10 ) == r.PHONE3.Substring( 0 ,10 ) ) && r.INACTIVE == 0 )