question

JimPerry-7042 avatar image
0 Votes"
JimPerry-7042 asked ·

How to solve this Azure App Service error querying Azure SQL DB

I have a Xamarin app that hits an Azure App Service that queries a SQL DB in Azure.

I'm using the Nuget Microsoft.Azure.Mobile.Client package in my app

I started with the standard Quick Start code and modified it to fit my app. I believe the problem is in the schema that I'm using to keep my tables separate from others in the DB, but I can't find any definitive info on specifying a schema in my code that queries the DB. I did change the connection string to use a login that has a user that defaults to the schema, but that didn't solve it. Here's the relevant code:

Data Object:

 public class Game : EntityData
 {
    public int ID { get; set; }
    public string Name { get; set; }
    public int Genre { get; set; }
    public string URL { get; set; }
 }

SQL Table:

3871-game-table.png

App Code:

   public partial class RPGGamersHubService
     {
         public string ApplicationURL = @"http://rpggamershub.azurewebsites.net/";
    
         static RPGGamersHubService defaultInstance = new RPGGamersHubService();
         MobileServiceClient client;
         IMobileServiceTable<Game> gamesTable;
    
         private RPGGamersHubService()
         {
             this.client = new MobileServiceClient(ApplicationURL);
         }
    
         public static RPGGamersHubService DefaultManager
         {
             get
             {
                 return defaultInstance;
             }
             private set
             {
                 defaultInstance = value;
             }
         }
    
         public MobileServiceClient CurrentClient
         {
             get { return client; }
         }
    
         public async Task<ObservableCollection<Game>> GetGamesAsync(bool syncItems = false)
         {
             try
             {
                 gamesTable = client.GetTable<Game>();
    
                 IEnumerable<Game> items = await gamesTable
                     .ToEnumerableAsync();
    
                 return new ObservableCollection<Game>(items);
             }
             catch (MobileServiceInvalidOperationException msioe)
             {
                 Debug.WriteLine("Invalid sync operation: {0}", new[] { msioe.Message });
             }
             catch (Exception e)
             {
                 Debug.WriteLine("Sync error: {0}", new[] { e.Message });
             }
             return null;
         }
     }

Service Context:

 public class RPGGamersHubServiceContext : DbContext
 {
     // You can add custom code to this file. Changes will not be overwritten.
     // 
     // If you want Entity Framework to alter your database
     // automatically whenever you change your model schema, please use data migrations.
     // For more information refer to the documentation:
     // http://msdn.microsoft.com/en-us/data/jj591621.aspx
    
     private const string connectionStringName = "Name=RPGGamersHubConnectionString";
    
     public RPGGamersHubServiceContext() : base(connectionStringName)
     {
     } 
    
     public DbSet<Game> Games { get; set; }
     public DbSet<User> Users { get; set; }
     public DbSet<Event> Events { get; set; }
     public DbSet<Module> Modules { get; set; }
     public DbSet<Character> Characters { get; set; }
     public DbSet<UserEvent> UserEvents { get; set; }
     public DbSet<EventRating> EventRatings { get; set; }
     public DbSet<User> Friends { get; set; }
    
     protected override void OnModelCreating(DbModelBuilder modelBuilder)
     {
         modelBuilder.Conventions.Add(
             new AttributeToColumnAnnotationConvention<TableColumnAttribute, string>(
                 "ServiceTableColumn", (property, attributes) => attributes.Single().ColumnType.ToString()));
     }
 }

Service Controller:

 public class GamesController : TableController<Game>
 {
     protected override void Initialize(HttpControllerContext controllerContext)
     {
         base.Initialize(controllerContext);
         RPGGamersHubServiceContext context = new RPGGamersHubServiceContext();
         DomainManager = new EntityDomainManager<Game>(context, Request);
     }
    
     // GET tables/TodoItem
     public IQueryable<Game> GetAllGames()
     {
         return Query();
     }
    
     // GET tables/TodoItem/48D68C86-6EA6-4C25-AA33-223FC9A27959
     public SingleResult<Game> GetGame(string id)
     {
         return Lookup(id);
     }
    
     // PATCH tables/TodoItem/48D68C86-6EA6-4C25-AA33-223FC9A27959
     public Task<Game> UpdateGame(string id, Delta<Game> patch)
     {
         return UpdateAsync(id, patch);
     }
    
     // POST tables/TodoItem
     public async Task<IHttpActionResult> AddGame(Game item)
     {
         Game current = await InsertAsync(item);
         return CreatedAtRoute("Tables", new { id = current.ID }, current);
     }
    
     // DELETE tables/TodoItem/48D68C86-6EA6-4C25-AA33-223FC9A27959
     public Task DeleteGame(string id)
     {
         return DeleteAsync(id);
     }
 }

The View I'm showing:

 [XamlCompilation(XamlCompilationOptions.Compile)]
 public partial class GamesListPage : ContentPage
 {
     public ObservableCollection<Game> Games { get; set; }
    
     public GamesListPage()
     {
         InitializeComponent();
    
         Games = new GamesViewModel().Games;
    
         MyListView.ItemsSource = Games;
     }
    
     async void Handle_ItemTapped(object sender, ItemTappedEventArgs e)
     {
         if (e.Item == null)
             return;
    
         await DisplayAlert("Item Tapped", "An item was tapped.", "OK");
    
         //Deselect Item
         ((ListView)sender).SelectedItem = null;
     }
 }
 The ViewModel I'm using:
    
    public class GamesViewModel : BaseViewModel
     {
         public ObservableCollection<Game> Games { get; set; }
         public Command LoadGamesCommand { get; set; }
    
         public GamesViewModel()
         {
             Title = "Games";
             Games = new ObservableCollection<Game>();
             LoadGamesCommand = new Command(async () => await ExecuteLoadGamesCommand());
    
             LoadGamesCommand.Execute(null);
         }
    
         async Task ExecuteLoadGamesCommand()
         {
             if (IsBusy)
                 return;
    
             IsBusy = true;
    
             try
             {
                 Games.Clear();
                 var games = await RPG_Gamers_Hub.Services.RPGGamersHubService.DefaultManager.GetGamesAsync();
                 foreach (var game in games)
                 {
                     Games.Add(game);
                 }
             }
             catch (Exception ex)
             {
                 Debug.WriteLine(ex);
             }
             finally
             {
                 IsBusy = false;
             }
         }
     }

The error I'm getting is:

> Microsoft.WindowsAzure.MobileServices.MobileServiceInvalidOperationException: 'The resource you are looking for has been removed, had its name changed, or is temporarily unavailable.'

occurs here:

 IEnumerable<Game> items = await gamesTable
     .ToEnumerableAsync();

in the first chunk of code above in the GetGamesAsync method.

Any idea what am I missing?








azure-webapps
game-table.png (4.2 KiB)
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ryanchill avatar image
0 Votes"
ryanchill answered ·

HI @JimPerry-7042,

I would try adding the Table attribute to your EntityData object

 [Table("Game", Schema="RPGGamersHub")]
 public class Game : EntityData
 {
     …
 }

You can also add modelBuilder.HasDefaultSchema("RPGGamersHub") to your OnModelCreating override method. I would also check fiddler when you run your API service locally to see how the request looks with the schema tied to it.

Hope this helps.

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the reply. Unfortunately, neither of those worked. The URI that's getting sent is still - https://rpggamershub.azurewebsites.net/tables/Game

I did rename the tables on the server to have the plural as I read EF automatically appends an "s" to the models when doing queries. That still didn't solve the issue.

I can't believe something like this hasn't been accounted for.

0 Votes 0 ·