1) JOIN in Azure Table
I am having RDBMS table name like SubscriptionFeatures, SubscriptionGroups, SubscriptionPlans,
Subscriptions, Roles etc.
Please find details below
/****** Object: Table [dbo].[SubscriptionPlan] Script Date: 05/09/2012 15:14:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SubscriptionPlan](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](256) NOT NULL,
[Description] [nvarchar](max) NULL,
[Status] [char](1) NOT NULL,
CONSTRAINT [PK_SubscriptionPlan] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Subscription] Script Date: 05/09/2012 15:14:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Subscription](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FromDate] [datetime] NULL,
[ToDate] [datetime] NULL,
[Status] [char](1) NOT NULL,
[DateOfSubscription] [datetime] NOT NULL,
[TimeZone] [time](7) NOT NULL,
[SubscriptionPlan_Id] [int] NOT NULL,
CONSTRAINT [PK_Subscription] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[SubscriptionGroup] Script Date: 05/09/2012 15:14:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SubscriptionGroup](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](256) NOT NULL,
[Description] [nvarchar](max) NULL,
[SubscriptionPlan_Id] [int] NOT NULL,
CONSTRAINT [PK_SubscriptionGroup] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SubscriptionFeature] Script Date: 05/09/2012 15:14:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SubscriptionFeature](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](256) NOT NULL,
[IsOptional] [bit] NOT NULL,
[Description] [nvarchar](max) NULL,
[SubscriptionGroup_Id] [int] NOT NULL
)
GO
/****** Object: Table [dbo].[Role] Script Date: 05/09/2012 15:14:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Role](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](256) NOT NULL,
[Description] [nvarchar](max) NOT NULL,
[Status] [char](1) NOT NULL,
[Subscription_Id] [int] NOT NULL,
CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: ForeignKey [FK_SubscriptionRole] Script Date: 05/09/2012 15:14:20 ******/
ALTER TABLE [dbo].[Role] WITH CHECK ADD CONSTRAINT [FK_SubscriptionRole] FOREIGN KEY([Subscription_Id])
REFERENCES [dbo].[Subscription] ([Id])
GO
/****** Object: ForeignKey [FK_SubscriptionPlanSubscription] Script Date: 05/09/2012 15:14:20 ******/
ALTER TABLE [dbo].[Subscription] WITH CHECK ADD CONSTRAINT [FK_SubscriptionPlanSubscription] FOREIGN KEY([SubscriptionPlan_Id])
REFERENCES [dbo].[SubscriptionPlan] ([Id])
GO
/****** Object: ForeignKey [FK_SubscriptionPlanSubscriptionGroup] Script Date: 05/09/2012 15:14:20 ******/
ALTER TABLE [dbo].[SubscriptionGroup] WITH CHECK ADD CONSTRAINT [FK_SubscriptionPlanSubscriptionGroup] FOREIGN KEY([SubscriptionPlan_Id])
REFERENCES [dbo].[SubscriptionPlan] ([Id])
GO
/****** Object: ForeignKey [FK_SubscriptionGroupSubscriptionFeature] Script Date: 05/09/2012 15:14:20 ******/
ALTER TABLE [dbo].[SubscriptionFeature] WITH CHECK ADD CONSTRAINT [FK_SubscriptionGroupSubscriptionFeature] FOREIGN KEY([SubscriptionGroup_Id])
REFERENCES [dbo].[SubscriptionGroup] ([Id])
Currently I am having below query with join among different tables to get required data.
var subscriptionFeatures = (from sf in context.SubscriptionFeatures
from sg in context.SubscriptionGroups
where sf.SubscriptionGroup.Id == sg.Id
from sp in context.SubscriptionPlans
where sg.SubscriptionPlan.Id == sp.Id
from s in context.Subscriptions
where s.SubscriptionPlan.Id == sp.Id
from r in context.Roles
where r.Subscription.Id == s.Id
where r.Id == roleId
where r.Status == "Y" && s.Status == "Y" &&
sp.Status == "Y"
select new
{
SubscriptionFeatureId = sf.Id,
SubscriptionFeatureName = sf.Name,
Description = sf.Description,
IsOptional = sf.IsOptional,
SubscriptionGroupId = sg.Id,
SubscriptionGroupName = sg.Name,
SubscriptionPlanId = sp.Id,
SubscriptionPlanName = sp.Name
});
This is my azure table structure.
Can you please send me the code snippet to get the same result set using my below mentioned azure tables
Field Name | PK | RK | Kind | Feature_Id | Name | Description | Status | FromDate | ToDate | DateOfSubscription |
subscriptionPlan | subscriptionPlan | sp_GUID1 | sp |
| Music Store Basic | Basic Plan | Y |
|
|
|
subscriptionPlan | sp_GUID2 | sp |
| Music Store Professional | Music Store Professional | Y |
|
|
| |
subscriptionPlan | sp_GUID3 | sp |
| Music Store Ultimate | Music Store Ultimate | Y |
|
|
| |
subscriptionPlan | sp_GUID4 | sp |
| Silver | Silver Plan | Y |
|
|
| |
subscriptionPlan | sp_GUID5 | sp |
| Gold | Golden Plan | Y |
|
|
| |
subscriptionPlan | sp_GUID6 | sp |
| Diamond | Diamond Plan | Y |
|
|
| |
Subscription | sp_GUID1 | s_GUID1 | s |
|
|
|
| 3/7/2011 0:00 | 3/7/2036 0:00 | 3/7/2011 0:00 |
sp_GUID1 | s_GUID2 | s |
|
|
|
| 3/7/2011 0:00 | 3/7/2036 0:00 | 3/7/2011 0:00 | |
sp_GUID1 | s_GUID3 | s |
|
|
|
| 3/7/2011 0:00 | 3/7/2036 0:00 | 3/7/2011 0:00 | |
sp_GUID1 | s_GUID4 | s |
|
|
|
| 3/31/2011 0:00 | 4/30/2011 0:00 | 3/31/2011 12:36 | |
sp_GUID2 | s_GUID5 | s |
|
|
|
| 6/15/2011 0:00 | 5/31/2013 0:00 | 6/15/2011 10:09 | |
sp_GUID3 | s_GUID6 | s |
|
|
|
| 6/17/2011 0:00 | 6/30/2011 0:00 | 6/17/2011 4:42 | |
subscriptionGroup | sp_GUID1 | sg_GUID1 | sg |
| Shopping Cart | Shopping Cart | Y |
|
|
|
sp_GUID1 | sg_GUID2 | sg |
| Shopping Cart | Shopping Cart | Y |
|
|
| |
sp_GUID2 | sg_GUID3 | sg |
| Default Group | Default Group | Y |
|
|
| |
sp_GUID3 | sg_GUID4 | sg |
| Book | Book Page Group | Y |
|
|
| |
subscriptionFeature | sg_GUID1 | sf_GUID1 | sf |
|
| Home Page | Y |
|
|
|
sg_GUID1 | sf_GUID2 | sf | f_GUID1 | About | About Page | Y |
|
|
| |
sg_GUID2 | sf_GUID3 | sf | f_GUID1 | View Book | Book Page | Y |
|
|
| |
sg_GUID3 | sf_GUID4 | sf | f_GUID2 | Edit Book | Edit Book | Y |
|
|
| |
sg_GUID4 | sf_GUID5 | sf | f_GUID3 | Login to application | Login to application | Y |
|
|
| |
Field Name | PK | RK | Name | Description | Status | |||||
Role | s_GUID1 | R_GUID1 | Administrator | Administrator | Y | |||||
s_GUID1 | R_GUID2 | Administrator | Administrator | Y | ||||||
s_GUID2 | R_GUID3 | Administrators | Music Store Administrators | Y |
2) I am not getting any code snippet for EGT Azure Table. If possible please send code snippet to use EGT.
Below mentioned code is working fine
public bool UpdateSubscriptionFeature(SubscriptionFeature subscriptionFeature)
{
bool status = false;
try
{
string AzureTableName;
AzureTableName = "WASubscriptionDtls";//Windows Azure Table Name
DataSource objDataSource;
objDataSource = new DataSource(AzureTableName);
WASubscriptionDtls subscriptionFeatureEntity = (from sf in
objDataSource.WASubscriptions()
where sf.Kind == DataModelConstant.subscriptionFeature
select sf).FirstOrDefault();
WASubscriptionDtls featureEntity = (from f in
objDataSource.WASubscriptions()
where f.Kind == DataModelConstant.subscriptionPlan && f.Status == "Y"
select f).FirstOrDefault();
if (subscriptionFeatureEntity != null)
{
subscriptionFeatureEntity.Name = subscriptionFeature.Name;
subscriptionFeatureEntity.Description =
subscriptionFeature.Description;
subscriptionFeatureEntity.IsOptional =
subscriptionFeature.IsOptional;
objDataSource.Update(subscriptionFeatureEntity);
objDataSource._serviceContext.SaveChangesWithRetries();
if (featureEntity != null)
{
featureEntity.Name = subscriptionFeature.Feature.Name;
featureEntity.Code = subscriptionFeature.Feature.Code;
objDataSource._serviceContext.UpdateObject(featureEntity);
objDataSource._serviceContext.SaveChangesWithRetries();
}
}
status = true;
}
catch (Exception ex)
{
}
return status;
}
Error coming once we are using ExecuteBatch
public bool UpdateSubscriptionFeature(SubscriptionFeature subscriptionFeature)
{
bool status = false;
try
{
string AzureTableName;
AzureTableName = "WASubscriptionDtls";//Windows Azure Table Name
DataSource objDataSource;
objDataSource = new DataSource(AzureTableName);
WASubscriptionDtls subscriptionFeatureEntity = (from sf in objDataSource.WASubscriptions()
where sf.Kind == DataModelConstant.subscriptionFeature select sf).FirstOrDefault();
WASubscriptionDtls featureEntity = (from f in objDataSource.WASubscriptions()
where f.Kind == DataModelConstant.subscriptionPlan && f.Status == "Y"
select f).FirstOrDefault();
if (subscriptionFeatureEntity != null)
{
subscriptionFeatureEntity.Name = subscriptionFeature.Name;
subscriptionFeatureEntity.Description = subscriptionFeature.Description;
subscriptionFeatureEntity.IsOptional = subscriptionFeature.IsOptional;
objDataSource.Update(subscriptionFeatureEntity);
if (featureEntity != null)
{
featureEntity.Name = subscriptionFeature.Feature.Name;
featureEntity.Code = subscriptionFeature.Feature.Code;
objDataSource._serviceContext.UpdateObject(featureEntity);
}
} objDataSource._serviceContext.ExecuteBatch();
status = true;
} catch (Exception ex)
{
} return status; }
