• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1353
  • Last Modified:

Left Outer Join in Linq

I am working a lightswitch project and I need a left outer join to get a couple of phone numbers. I get an error that says
Definition Of Method 'SelectMany' Is Not Accessable In This Context.
I can't find an answer to how to fix it so far.
Please help.
Private Sub PhoneNumbers_PreprocessQuery(ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.UserProfile))
			query = From up In UserProfiles
					From ppd In ProfilePropertyDefinitions
					Where up.ProfilePropertyDefinition.PropertyDefinitionID = ppd.PropertyDefinitionID And ppd.PropertyName = "Telephone"
					From ppd In ProfilePropertyDefinitions
					Where up.ProfilePropertyDefinition.PropertyDefinitionID = ppd.PropertyDefinitionID And ppd.PropertyName = "Cell"
					Select ppd.PropertyName = "Telephone", ppd.PropertyName = "Cell"
End Sub

Open in new window

0
wdarnellg
Asked:
wdarnellg
  • 5
  • 3
1 Solution
 
wdarnellgAuthor Commented:
Thanks for the links. A couple were very cool to discover. However, when I change FROM to JOIN, I get the error: Definition Of Method 'JOIN' Is Not Accessable In This Context.
I am guessing that some how my entity(ies) is/are not .IQueryable... I don't understand why.
0
 
wdarnellgAuthor Commented:
This join cannot be done in Lightswitch. The joins have to be done using the WCF RIA Service.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
MikeTooleCommented:
I don't quite get what the Sub is supposed to do.
An IQueryable instance, query, is supplied as a  ByRef parameter, but is then set to a new instance of a LINQ query.
AFAIK the new instance assigned to query will not be visible outside the Sub (ByRef allows changes to an object, not replacement of it)
0
 
wdarnellgAuthor Commented:
I was hoping to extract related data from two existing tables that have different parts of the data. In researching I found this article by Michael Washington that says "LightSwitch operates on one Entity (table) at a time. A Custom Control will allow you to visualize data from two entities at the same time, but inside LightSwitch, each Entity is always separate. This can be a problem if you want to, for example, combine two Entities into one."

I guess that is why I get the error. The context that the error message mentions seems to be Lighswitch itself. So, I am going to look into another option.
0
 
MikeTooleCommented:
In this case you can easily satify your criteria by prefetching the IDs for the properties you're interested in, something like:
dim idTel = (From ppd In ProfilePropertyDefinitions
         Where ppd.PropertyName = "Telephone"
                  Select ppd.PropertyDefinitionID).FIrst()
or, more easily, via Method Chaining
dim idTel = ProfilePropertyDefinitions.First(Function(of ppd) ppd.PropertyDefinitionID = "Telephone")

Then use the values returned in the Where clause of a single entity query.

Caveat: The above LINQ is off the top of my head and not necessarily 100% correct :)
0
 
wdarnellgAuthor Commented:
That query works fine, but it doesn't join the UserProfile and the ProfileProperty tables to get the property name from the ppd table and the related propertyValue from the up table; which I just realized I forgot to select the up.propertyvalue.

The problem is JOINING the tables to link the related fields. I don't know why dotnetnuke did it this way, but it is what it is. :o)
query = (From up In UserProfiles
					From ppd In ProfilePropertyDefinitions
					Where up.ProfilePropertyDefinition.PropertyDefinitionID = ppd.PropertyDefinitionID And ppd.PropertyName = "Telephone"
					From ppd In ProfilePropertyDefinitions
					Where up.ProfilePropertyDefinition.PropertyDefinitionID = ppd.PropertyDefinitionID And ppd.PropertyName = "Cell"
					Select ppd.PropertyName = "Telephone", ppd.PropertyName = "Cell", up.PropertyValue)

Open in new window

0
 
MikeTooleCommented:
My idea was to avoid the Join altogether by retrieving the required PropertyDefinitionIDs in seperate single-table queries on ProfilePropertyDefinitions, then using those values in a subsequent single table query on UserProfiles.
 
0
 
wdarnellgAuthor Commented:
This is an example of the table data that I am trying to unite as readable data. These are not my tables. I would have done my profiles differently, but I am not a professional developer and I am sure they have their reasons. I can unite everything easily with sql. LightSwitch doesn't seem to want to allow linq to sql to do it.

 TablesToJoin.pdf
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now