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

Zukoh · September 2017

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 · September 2017

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 · September 2017

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 · September 2017

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 · September 2017

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 · September 2017

@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 · September 2017

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
	)

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

Comments